Can you please tell me what is wrong in this query? I am trying to write a SQL query to create a table dynamically


SET @SQLString =+ 'Create Table' + GETDATE() + '_' + 'Table' + '' + '(' + 'Column1' + ' ' + 'Nvarchar(50)' + 'Null' + ')' +

Exec @SQLString ;

2 Answers 2


There are multiple issues with your string @SQLString:

  1. You don't require + at the beginning and at the end
  2. A date will not concatenate with a nvarchar without conversion (CAST or CONVERT)
  3. Because your table will contain spaces you need to put the table name in square brackets: [ and ].
  4. You will be better off with exec sp_executesql @SQLString

So your script might work with this:

SET @SQLString = 'Create Table [' + CAST(GETDATE() AS NVARCHAR(30))+ '_Table]' + ' (' + 'Column1' + ' ' + 'Nvarchar(50) Null' + ')' 
-- Exec sp_executesql @SQLString

I'd recommend reading up on the CREATE TABLE statement and on the String Functions. Then carry on to the Data Type Conversion documentation and have a glimpse at the sp_executesql syntax.

Good Luck.

  • 2
    Right on the money here. Also, does the | ' (' + 'Column1' + ' ' + 'Nvarchar(50) Null' + ')' | portion seem a bit excessive in the concatenation? Technically it can just be | ' (Column1 Nvarchar(50) Null) ' | right. I mean I see the value if the Column1 were a variable or parameter, but here it doesn't make much sense.
    – SQLDevDBA
    Jul 17, 2017 at 12:47
  • Yes. That is why I left it out in the code I posted.
    – John K. N.
    Jul 17, 2017 at 13:38

On technique - I prefer to write one string in the program or script and then use REPLACE to insert variable terms using token marks where they go, i.e.

SET @template = 
CREATE TABLE @{table} 
(i int, j int)
SET @table_name_q = QUOTENAME(<formula>)
SET @workstring = 
                , N'@{table}', @table_name_q)
                ...more lines as necessary
EXEC sp_executesql @workstring [ , @parameters, @parameter1, @parameter2, ...]

Sometimes I use my own "safe_exec" procedure which tests the string for ending with "--x" and not containing any "@{" left in by mistake, and raises errors for failing these tests, instead of proceeding to execute with sp_executesql.

  • I've got to be honest, this looks a bit cumbersome. I see where you're going with it after reading it for a while, but you always want to code for the next developer to have an easy time maintaining your code when you hit the lottery jackpot.
    – SQLDevDBA
    Jul 17, 2017 at 21:51
  • I find my way is good for larger exercises - though now I remember, I have found it necessary to put a key in a comment inside the string, like /* @{table} = [DB01].[dbo].[Table_Year2016] */' for each of the tokens. At run time of course it could be DB27 and Year2017. Lately somehow I've done less of this work. A colleague prefers %%TABLE%% as his token style in otherwise similar work; if I have to rewrite most of one of those, I'll change it to my style. If he doesn't like that, he should have done it right :-) Jul 17, 2017 at 23:20
  • Hahahaha can't argue with that!
    – SQLDevDBA
    Jul 17, 2017 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.