SQL Server : Msg 102, Level 15, State 1, Line 2 In

问题:

I'm writing a stored procedure and I'm passing the table names as parameters, but I'm having an error in this part:

DECLARE 
@TableA nvarchar(255)='TableA',
@DOCID1 nvarchar(MAX),
@DOCID2 int;

EXEC ('
SELECT TOP (1) '+ @DOCID1 +'=DOCID1,'+ @DOCID2 +'=DOCID2
FROM [' + @TABLEA + ']
ORDER BY DOCID2')

After I run this query I get this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='

I have tried and I can't pinpoint the error, at this point I need some help..

回答1:

I believe you have to concatenate together your SQL statement as a whole, before executing it:

DECLARE 
    @TableA nvarchar(255)='TableA',
    @DOCID1 nvarchar(MAX),
    @SqlStmt NVARCHAR(500),
    @DOCID2 int;

SET @SqlStmt = N'SELECT TOP (1) ' + @DOCID1 + N' = DOCID1, ' + @DOCID2 + N' = DOCID2 FROM [' + @TABLEA + N'] ORDER BY DOCID2';

EXEC (@SqlStmt)

As far as I recall, you cannot have expressions and computations inside the EXEC command - get the statement prepared before hand, then execute it

Also, I'm not entirely sure what those variables of yours hold - @DocID1 and @DocID2 - do you want to set their value, or do they hold the name of another variable to set??

Update: if you actually wanted to set the values of @DocID1 and @DocID2, then your query was wrong to begin with - then you need something like this:

DECLARE 
    @TableA nvarchar(255) = 'TableA',
    @SqlStmt NVARCHAR(500);

SET @SqlStmt = 
    N'DECLARE @DocID1 NVARCHAR(MAX), @DocID2 INT; ' +
    N'SELECT TOP (1) @DOCID1 = DOCID1, @DOCID2 = DOCID2 FROM [' + @TABLEA + N'] ORDER BY DOCID2';

EXEC (@SqlStmt)

but then, those two variables are scoped inside the dynamically executed SQL and aren't available to the "outside" of your script.

回答2:

What version of SQL Server? The sytax -

DECLARE 
@TableA nvarchar(255)='TableA'

is supported only from SQL Server 2008. For older versions you have to write:

DECLARE 
@TableA nvarchar(255)
SET @TableA ='TableA'