How to execute SQL Dynamic query String from table row?

Question

I have an awesome query that populates my virtual table with a list of scripts that create non clustered indices for all foreign keys.

However this list is great but, I want to take it another step further and run a dynamic query to execute each script.

I don't really have that much exp with dynamic query's as most time I simply replicate functionality in my c# software instead.

So I have..

DECLARE @SQLquery as NVARCHAR(MAX)

SET @SQLquery = (SELECT * FROM #ForiegnKeyScriptsNumero)

EXECUTE @SQLquery

DROP TABLE #ForiegnKeyScriptsNumero

now this is not going to work as im returning mulitple rows.

What I need to do is to simply get each row and execute one after the other.

For the life of me my brain has had a melt down and I just cannot see it.

I know this is a simple fix, what am I missing?

UPDATE ::

Just to add here's what my script generates in each row of my table, table has just 1 column.

IF NOT EXISTS 
    (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ActivityLog]') 
     AND name = N'IX_ActivityLogTypeId') CREATE NONCLUSTERED INDEX  [IX_ActivityLogTypeId] ON [dbo].[ActivityLog]( [ActivityLogTypeId] ASC ) 
     WITH (PAD_INDEX  = OFF, 
           STATISTICS_NORECOMPUTE  = OFF, 
           SORT_IN_TEMPDB = OFF, 
           IGNORE_DUP_KEY = OFF, 
           DROP_EXISTING = OFF, 
           ONLINE = OFF, 
           ALLOW_ROW_LOCKS  = ON, 
           ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

This code is what is in my rows.

As a treat I thought I would post this script that generates the scripts.

CREATE TABLE #ForiegnKeyScriptsRun (scripts nvarchar(max))
INSERT INTO #ForiegnKeyScriptsRun
SELECT
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['
+ tab.[name]
+ ']'') AND name = N''IX_'
+ cols.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ cols.[name]
+ '] ON [dbo].['
+ tab.[name]
+ ']( ['
+ cols.[name]
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,   IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
FROM
    sys.foreign_keys keys 
INNER JOIN 
    sys.foreign_key_columns keyCols
 ON keys.object_id = keyCols.constraint_object_id
INNER JOIN 
    sys.columns cols
 ON keyCols.parent_object_id = cols.object_id
AND 
    keyCols.parent_column_id = cols.column_id
INNER JOIN 
    sys.tables tab
 ON keyCols.parent_object_id = tab.object_id
ORDER BY tab.[name], cols.[name]

OK, I followed JKN solution but the generated code when checked does not like IF

EXEC IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ActivityLog]') AND name = N'IX_ActivityLogTypeId') CREATE NONCLUSTERED INDEX [IX_ActivityLogTypeId] ON [dbo].[ActivityLog]( [ActivityLogTypeId] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

the error is "Incorrect syntax near the keyword 'IF'." Again I am not sure what syntax is needed for dynamic sql, is there some syntax missing? Can anyone spot this?

Andriy M · Accepted Answer · 2014-01-16 08:43:19Z

8

You can use this syntax to build a string from a list of rows. Please be advised it's not officially supported and may cause issues if you start using things like ORDER BY:

DECLARE @SQLquery as NVARCHAR(MAX) = ''

SELECT @SQLquery += 'EXEC ' + [ScriptColumn] + CHAR(10) + CHAR(13)
FROM #ForiegnKeyScriptsNumero

-- Check it first!
SELECT @SQLQuery
-- EXECUTE (@SQLquery)

edited Jan 16, 2014 at 8:43

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jan 10, 2014 at 16:15

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

lemunk Over a year ago

well at the moment my FK script generates a list of sql scripts. in practically the same way, im thinking i can use this and convert my script so it does it in one go.... nice!!!!

JNK Over a year ago

@StevenSmith if any of your rows are NULL in that table it will make the whole thing null

JNK Over a year ago

Also you need to initialize the string as '', please see updated answer

lemunk Over a year ago

sweet holy lord! works beautifully!! pretty much does what the script generator does! only runs it. want me to post the generator? i reckon i can skip the whole population in my virtual data combining your script.

Aaron Bertrand Over a year ago

The EXECUTE @SQLquery needs to be EXECUTE(@SQLquery) (or use sp_executesql).

|

Collectives™ on Stack Overflow

How to execute SQL Dynamic query String from table row?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related