SQL Server：消息102，级别15，状态1，第2行'='附近的语法不正确 [英] SQL Server : Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '='

问题描述

我正在编写一个存储过程，并且将表名作为参数传递，但是这部分出现错误：

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:

消息102，级别15，状态1，第2行

'='附近的语法不正确

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..

推荐答案

我相信您必须先将SQL语句作为一个整体串联在一起，然后再执行：

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)

据我所知，您不能在 EXEC 命令中包含表达式和计算-事先准备好语句，然后执行

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

另外，我我不完全确定您的那些变量持有什么- @ DocID1 和 @ DocID2 -您是否要设置它们

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??

更新：如果您确实想设置 @ DocID1 和 @ DocID2 ，那么您的查询开头是错误的-那么您需要这样的内容：

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)

但是，这两个变量的作用域内部动态执行的SQL，并且脚本的外部不可用。

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

这篇关于SQL Server：消息102，级别15，状态1，第2行'='附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！