1

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

DECLARE @SQLString NVARCHAR(MAX)

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

Exec @SQLString ;
7

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:

DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'Create Table [' + CAST(GETDATE() AS NVARCHAR(30))+ '_Table]' + ' (' + 'Column1' + ' ' + 'Nvarchar(50) Null' + ')' 
PRINT @SQLString
-- 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.

| improve this answer | |
  • 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. – MguerraTorres Jul 17 '17 at 12:47
  • Yes. That is why I left it out in the code I posted. – John aka hot2use Jul 17 '17 at 13:38
0

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 = 
N'
CREATE TABLE @{table} 
(i int, j int)
;--x'
SET @table_name_q = QUOTENAME(<formula>)
SET @workstring = 
        REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(
                  @template
                , 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.

| improve this answer | |
  • 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. – MguerraTorres Jul 17 '17 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 :-) – Robert Carnegie Jul 17 '17 at 23:20
  • Hahahaha can't argue with that! – MguerraTorres Jul 17 '17 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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