我尝试从表中选择一行,并将其值影响到本地声明的变量,我使用下面的方法,但我得到了错误。
方法:
DECLARE @A AS INT ;
SET @A=1;
WHILE @A<=10
BEGIN
DECLARE @R AS TABLE
SET @R = (SELECT * FROM Client c WHERE c.ID=@A)
SET @A=@A+1
END
Error I got:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'SET'.
那么我怎样才能影响子查询结果到R局部变量?
表变量不能赋值;使用DML向表变量中添加数据。从文档
表(transact - sql)不支持表变量间的赋值操作
比如:
DECLARE @R AS TABLE(ClientID int, Name nvarchar(200), ...)
INSERT INTO @R(ClientId, Name, ...)
SELECT ClientId, Name, ...
FROM Client c
WHERE c.ID=@A