CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Erran Morad
Erran Morad

Reputation: 4743

Dynamic SQL is correct, but SP_EXECUTESQL will not execute it?

The following code execute SQL n times. 

PRINT 'X'
GO 10

I try to make this a dynamic codes with following query - 

DECLARE @rows int 
DECLARE @sql nvarchar(max)
SET @rows = 10
SET @sql =
'INSERT INTO MultiInsert(Name)
VALUES(NULL)
GO ' + CAST(@rows as NVARCHAR(50))
PRINT @sql
EXEC SP_EXECUTESQL @sql

The SQL is a correct, but SP_EXECUTESQL give me an error as show below - 

INSERT INTO MultiInsert(Name)
VALUES(NULL)
GO 10
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.

How do I fix this error ?

Upvotes: 0

Views: 1511

Answers (3)

suff trek
suff trek

Reputation: 39777

You can use this kind of query to generate 10 NULLs in one run:

WITH mycte AS
(
   SELECT NULL as VAL
   UNION ALL
   SELECT NULL FROM mycte  
)
SELECT TOP 10 Val from mycte

So you would do something like

DECLARE @rows int 
DECLARE @sql nvarchar(max)
SET @rows = 10
SET @sql =

'WITH mycte AS
    (
       SELECT NULL as VAL
       UNION ALL
       SELECT NULL FROM mycte  
    )
    INSERT INTO MultiInsert(Name)
    SELECT TOP ' + CAST(@rows as NVARCHAR(50)) + ' Val from mycte'

EXEC SP_EXECUTESQL @sql

In this case a recursive CTE with a single insert is used instead of a loop.

Upvotes: 2

Lamak
Lamak

Reputation: 70638

Well, @siride's answer shows you why it fails, and this is how you can fix it:

DECLARE @rows int 
DECLARE @sql nvarchar(max)
SET @rows = 10
SET @sql =
'DECLARE @I INT = 0
WHILE @I <= ' + CAST(@rows as NVARCHAR(50)) + '
BEGIN
    INSERT INTO MultiInsert(Name)
    VALUES(NULL)
    SET @I = @I + 1
END'

PRINT @sql

EXEC SP_EXECUTESQL @sql

Upvotes: 3

siride
siride

Reputation: 209495

You can't use GO in dynamic SQL expressions. GO is a keyword for management studio or other client tools.

Here's the MSDN documentation about GO, which explains how it's a client keyword only: http://technet.microsoft.com/en-us/library/ms188037.aspx. Notice this line in particular: "Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server."

Upvotes: 6

Related Questions