Creating a table using dynamic sql

Question

I want to use Dynamic SQL within a stored procedure to create a table.

Here is the call to the stored procedure:

EXEC [spCreateAColDiffTable] 'hq193.dbo.arch_con_col_s193_s202'

Here are the relevant parts of the stored procedure:

CREATE PROCEDURE sp_createAColDiffTable (@table_name nvarchar(128))

...

SET @sSQL = 'CREATE TABLE ' + @table_name + ' ( ' +
' [table_name]       VARCHAR (128) NOT NULL, ' +
' [column_name]      VARCHAR (128) NULL, ' +
' [what_changed]     VARCHAR (128) NULL, ' +
' [sr_data_type]     VARCHAR (128) NOT NULL, ' +
' [tr_data_type]     VARCHAR (128) NOT NULL, ' +
' [sr_max_length]    SMALLINT NOT NULL, ' +
' [tr_max_length]    SMALLINT NOT NULL, ' +
' [sr_is_nullable]   CHAR NULL, ' +
' [tr_is_nullable]   CHAR NULL, ' +
' [sr_precision]     SMALLINT NULL, ' +
' [tr_precision]     SMALLINT NULL, ' +
' [sr_scale]         SMALLINT NULL, ' +
' [tr_scale]         SMALLINT NULL ) ' +
' ON [PRIMARY] WITH (DATA_COMPRESSION = NONE)'
PRINT @sSQL
Exec @sSQL

GO

When I run the stored procedure I receive the error:

SQL Server Database Error: The name 'CREATE TABLE hq193.dbo.arch_con_col_s193_s202 ( [table_name] VARCHAR (128) NOT NULL, [column_name] VARCHAR (128) NULL, [what_changed] VARCHAR (128) NULL, [sr_data_type] VARCHAR (128) NOT NULL, [tr_data_type] VARCHAR (128) NOT NULL, [sr_max_length] SMALLINT NOT NULL, [tr_max_length] SMALLINT NOT NULL, [sr_is_nullable] CHAR NULL, [tr_is_nullable] CHAR NULL, [sr_precision] SMALLINT NULL, [tr_precision] SMALLINT NULL, [sr_scale] SMALLINT NULL, [tr_scale] SMALLINT NULL ) ON [PRIMARY] WITH (DATA_COMPRESSION = NONE)'

is not a valid identifier.

Notice in the stored procedure I printed out the SQL before I executed it. If I cut and paste the SQL that gets printed into a query editor window it works fine.

What am I missing?

score 14 · Accepted Answer · answered Oct 07 '13 at 14:44

14

Try it like this:

EXEC(@sSQL)

This is a very common error. Without the parenthesis, EXEC @sSQL means "execute a stored procedure whose name is in the @sSQL variable", rather than what you want which is probably "Execute the command string in the variable @sSQL."

answered Oct 07 '13 at 14:44

RBarryYoung

55,398
14
96
137

1

Although the answer is correct, I would suggest to use `exec sp_executesql @sSQL` for dynamic SQL. See [this](http://www.sommarskog.se/dynamic_sql.html) for more info. – seph Oct 07 '13 at 14:47
1

I am very familiar with Erland's work and writing and I am not aware of any good reason to use `sp_ExecuteSql` over `EXEC (..)` for this case. By it's nature I assume that this is an Operational procedure and not intended for use by Apps/OLTP, so SQL Injection should not be an issue (and `sp_ExecuteSql` alone wouldn't fix it in this case anyway). As for performance, this is DDL so the advantage of a pre-compiled query plan doesn't come into play here. – RBarryYoung Oct 07 '13 at 14:52
4

I prefer sp_executesql as it promotes a good habit. Why use one method where it matters and another where it doesn't, instead of always using the method that might matter? – Aaron Bertrand Oct 07 '13 at 14:54
@AaronBertrand I prefer not to use `sp_ExecuteSQL` in those cases where others might misunderstand the SQL Injection issues. specifically, I am concerned that others might believe that it is protecting against SQL Injection here when it definitely is not. This misunderstanding about `sp_ExecuteSQL` is very widespread and although I know that you understand the distinction, 90-99% of those who read/use my code do not understand that for a query like this, `sp_ExecuteSQL` provides no protection, but rather you need something like this: http://stackoverflow.com/a/1246848/109122 – RBarryYoung Oct 07 '13 at 15:26
1

Sorry but I disagree completely. I don't know why you would ever want to use EXEC - do you really, honestly believe that if you replace that with sp_executesql, people will suddenly think they're always protected from SQL injection? Again, I disagree. Promote best practices, not the ones that allow you to sweep peripheral issues under the rug. IMHO. – Aaron Bertrand Oct 07 '13 at 15:32
The main problem is, let's say we have `SET @sql = N'SELECT columns FROM ' + @table;` - and then later it becomes `SET @sql = N'SELECT columns FROM ' + @table + ' WHERE ID = ' + @Id;` - so only then will you advocate sp_executesql? Why not simply always use sp_executesql? – Aaron Bertrand Oct 07 '13 at 15:34

score 1 · Answer 2 · answered Nov 27 '15 at 18:50

I see this is an old post, but I've had a similar issue where I need to read a text file where the columns may of changed by having more and less depending on how the file was pulled. So I wrote a program to read the text file and put it into a dynamically created temp table where I can work with the output.

Perhaps this can help someone else..

DECLARE @NUM_COL    AS INT
DECLARE @I          AS INT
DECLARE @CREATE_TBL AS NVARCHAR(MAX)
DECLARE @DATA       AS NVARCHAR (MAX)
DECLARE @XML_ROW    AS XML
DECLARE @MAX_CHAR   AS INT

--Sets the column max characters for temp table ##FILE_TABLE
SET @MAX_CHAR = 1000

--File import of data as single rows, no columns
IF OBJECT_ID('tempdb..#FILE_ROWS') IS NOT NULL
DROP TABLE #FILE_ROWS

CREATE TABLE #FILE_ROWS
    ( [Row_data]  NVARCHAR(MAX) NULL )


--Global temp table used because the table is being built dynamically.
IF OBJECT_ID('tempdb..##FILE_TABLE') IS NOT NULL
DROP TABLE ##FILE_TABLE

--This is only so the debugger thinks the table is created when referenced in later SQL code.
IF 1 <> 1 CREATE TABLE ##FILE_TABLE (X INT)

BULK INSERT #FILE_ROWS
FROM 'C:\Users\Wayne\Desktop\777434633016764.txt'
WITH
(
  FIELDTERMINATOR = '\t'  --Tab Delimited
 ,ROWTERMINATOR = '\n'
)

--Figures out how many columns were in the file.
SET @NUM_COL = (SELECT MAX(LEN(Row_data) - LEN(REPLACE(Row_data, CHAR(9), ''))) + 1 AS [NO_COL] FROM #FILE_ROWS)

SET @CREATE_TBL = 'CREATE TABLE ##FILE_TABLE ( ID INT IDENTITY(1,1),'
SET @I = 1

Declare COUNTER Cursor for
        SELECT 
            CAST('<A>' + REPLACE(Row_data, CHAR(9), '</A><A>') + '</A>' AS XML)
        FROM #FILE_ROWS
open COUNTER
         fetch next from COUNTER into @XML_ROW
         while @@fetch_Status != -1
         begin

            IF @I = 1
            BEGIN
                SELECT @CREATE_TBL = @CREATE_TBL 
                                     + '[' + REPLACE(dbo.Trim(DATA.value('.','char(30)')), N'''', '`')
                                     + ']' + ' NVARCHAR(' + CAST(@MAX_CHAR AS NVARCHAR(5)) + ') NULL,' 
                FROM @XML_ROW.nodes('/A') AS x(DATA)

                SET @CREATE_TBL = LEFT(@CREATE_TBL, LEN(@CREATE_TBL) - 1) + ')'

                EXEC(@CREATE_TBL)

                SET @I = 2
            END
          --ELSE  --If you do not want the column names in the first row, remove the ELSE
            BEGIN
                SET @DATA = 'INSERT INTO ##FILE_TABLE SELECT '
                SELECT @DATA = @DATA 
                               + '''' + REPLACE(dbo.Trim(DATA.value('.','char(30)')), N'''', '`') 
                               + '''' + ','
                FROM @XML_ROW.nodes('/A') AS x(DATA)

                SET @DATA = LEFT(@DATA, LEN(@DATA) -1)

                EXEC(@DATA)
            END

            FETCH NEXT FROM COUNTER INTO @XML_ROW
         END
CLOSE COUNTER
DEALLOCATE COUNTER

SELECT * from ##FILE_TABLE

Creating a table using dynamic sql

2 Answers2

Linked