Need an efficient way to perform the given SQL query in SQL Server, stored procedure

0
votes

I have a problem where I have to create tables on basis of number of parameters passed in the stored procedure as follows:

----when there is one column
'CREATE TABLE [dbo].['+@table_name+'] (
                  ['+@colname1+'] [VARCHAR](200)
                  ) '
----when there are two column                     
'CREATE TABLE [dbo].['+@table_name+'] (
        ['+@colname1+'] [VARCHAR](200),
        ['+@colname2+'] [VARCHAR](200)
                  ) ;'

----when there are three column             
'CREATE TABLE [dbo].['+@table_name+'] (
                  ['+@colname1+'] [VARCHAR](200),
                  ['+@colname2+'] [VARCHAR](200),
                  ['+@colname3+'] [VARCHAR](200)
                  ) '

One way that I know of doing this is:

----when there is one column
If ((@colname1 IS NOT NULL AND LEN(@colname1) !=0)) 
BEGIN
'CREATE TABLE [dbo].['+@table_name+'] (
                  ['+@colname1+'] [VARCHAR](200)

                  )'
END
----when there are two column                     
If ((@colname1 IS NOT NULL AND LEN(@colname1) !=0) 
 AND (@colname2 IS NOT NULL AND LEN(@colname2) !=0)) 
BEGIN
Drop table [dbo].['+@table_name+']
'CREATE TABLE [dbo].['+@table_name+'] (
                  ['+@colname1+'] [VARCHAR](200),
                  ['+@colname2+'] [VARCHAR](200)
                  )'
END                   
----when there are three column             
If ((@colname1 IS NOT NULL AND LEN(@colname1) !=0) 
 AND (@colname2 IS NOT NULL AND LEN(@colname2) !=0)
 AND (@colname3 IS NOT NULL AND LEN(@colname3) !=0)) 
BEGIN
Drop table [dbo].['+@table_name+']
'CREATE TABLE [dbo].['+@table_name+'] (
                  ['+@colname1+'] [VARCHAR](200),
                  ['+@colname2+'] [VARCHAR](200),
                  ['+@colname3+'] [VARCHAR](200)
                  ) '
END

By using this way, my code becomes very lengthy. Is there any other efficient way to do this. If instead of passing values for @colname1, @colname2,@colname3 separately, I pass values in one single parmaeter @colname as @colname = 'col1,col2,col3', how can I do it?

2
sql-serverstored-procedures
This is probably a bad idea to begin with. create database objects such as tables in design time, not in run time. – Zohar Peled
What are you actually trying to achieve? Maybe there is a better way that doesn't involve the creation of tables dynamically. – Andy Skirrow
you can always start with one column, then conditionally perform "alter table add column" statements. – Jeremy
actually i have to create a script that takes in one column while creating tables , inserting into that table. Now i have to create the same script but for many columns – cnayak
could you elaborate, i am new to stored procedure. @Jeremy – cnayak

2 Answers

1
votes

It is possible to use dynamic sql and a comma delimited parameter for column names, though as I wrote in my comment, this is a bad idea, and I strongly recommend you to re-think this approach. using the "xml split string technique" and QUOTENAME to minimize sql injection hazard:

CREATE PROCEDURE stpCreateTableDynamically
(
    @Table_Name sysname, 
    @Column_Names nvarchar(max)
)

AS

    DECLARE @Sql nvarchar(max)

    SELECT @Sql = 'CREATE TABLE [dbo].' + QUOTENAME('zzz_'+ @Table_Name) +' ('

    SELECT @Sql = @Sql + QUOTENAME(split.a.value('.', 'VARCHAR(100)')) + ' [VARCHAR](200),'
    FROM   (SELECT Cast ('<M>' + Replace(@Column_Names, ',', '</M><M>')+ '</M>' AS XML) AS Data) AS A 
           CROSS apply data.nodes ('/M') AS Split(a); 

    SELECT @Sql = LEFT(@Sql, LEN(@Sql) - 1) + ') ON [ZZZ_FG];'

    PRINT @Sql
    --EXEC(@Sql)

GO

Usage: 

EXEC stpCreateTableDynamically 'MyTable', 'Column1,Col2,Col3'

Output: 

CREATE TABLE [dbo].[zzz_MyTable] ([Column1] [VARCHAR](200),[Col2] [VARCHAR](200),[Col3] [VARCHAR](200)) ON [ZZZ_FG];

Once you see that the output is ok, you can unremark the exec line and delete the print line.

1
votes

You can try like this,

    DECLARE @SQL NVARCHAR(max) = ''
    ,@table_name NVARCHAR(255) = 'Table1'
    ,@colname1 NVARCHAR(255) = 'c1'
    ,@colname2 NVARCHAR(255) = 'c2'
    ,@colname3 NVARCHAR(255) = 'c3'
    ,@cols NVARCHAR(max) = ''

SET @cols = CASE 
        WHEN @colname1 IS NOT NULL
            AND len(@colname1) > 0
            THEN ',[' + @colname1 + '] [VARCHAR](200)'
        ELSE ''
        END + CASE 
        WHEN @colname2 IS NOT NULL
            AND len(@colname2) > 0
            THEN ',[' + @colname2 + '] [VARCHAR](200)'
        ELSE ''
        END + CASE 
        WHEN @colname3 IS NOT NULL
            AND len(@colname3) > 0
            THEN ',[' + @colname3 + '] [VARCHAR](200)'
        ELSE ''
        END
SET @cols = stuff(@cols, 1, 1, '')
SET @SQL = 'Drop table [dbo].[zzz_' + @table_name + '] 
CREATE TABLE [dbo].[zzz_' + @table_name + '] (
' + @cols + '
) ON [ZZZ_FG];'

PRINT @sql
    --EXEC sp_executesql @sql