SQL Server：消息102，级别15，状态1，行2'='附近的语法不正确

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 '='

消息102,级别15,状态1,行2'='附近的语法不正确

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

我试过,我无法查明错误,此时我需要一些帮助..

2 个解决方案

#1

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

我相信在执行之前你必须将整个SQL语句连接在一起:

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

据我所知,你不能在EXEC命令中有表达式和计算 - 得到手前准备的语句,然后执行它

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:

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

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.

但是,这两个变量的作用域在动态执行的SQL中,并且不可用于脚本的“外部”。

#2

What version of SQL Server? The sytax -

什么版本的SQL Server? sytax -

DECLARE 
@TableA nvarchar(255)='TableA'

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

仅支持SQL Server 2008.对于旧版本,您必须编写:

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

#1