动态sql游标

Question

当我运行以下查询时，会得到“在'.‘’附近有”不正确的语法“。

DECLARE 
@StaffID INT = 4,
@Cursorsql nvarchar(4000);  

SET @Cursorsql = 'DECLARE curStaff CURSOR FOR ';
SET @Cursorsql = @Cursorsql + ' SELECT s.StaffID, o.OfficeName, s.FirstName '; 
SET @Cursorsql = @Cursorsql + ' ' + 's.LastName FROM Staff s  '; 
SET @Cursorsql = @Cursorsql + ' INNER JOIN Offices o ON o.OfficeID = s.OfficeID '; 
SET @Cursorsql = @Cursorsql + ' WHERE (s.OfficeID = @OfficeID OR @OfficeID = 0) '; 
IF @StaffID > 0 
 SET @Cursorsql = @Cursorsql + ' AND s.StaffID = @StaffID '; 
SET @Cursorsql = @Cursorsql + ' ORDER BY s.OfficeID, s.FirstName, s.LastName ';  

exec (@Cursorsql);

Answer 1

你在s.FirstName之后错过了一个逗号

DECLARE 
@StaffID INT = 4,
@Cursorsql nvarchar(4000);  

SET @Cursorsql = 'DECLARE curStaff CURSOR FOR ';
SET @Cursorsql = @Cursorsql + ' SELECT s.StaffID, o.OfficeName, s.FirstName, '; 
SET @Cursorsql = @Cursorsql + ' ' + 's.LastName FROM Staff s  '; 
SET @Cursorsql = @Cursorsql + ' INNER JOIN Offices o ON o.OfficeID = s.OfficeID '; 
SET @Cursorsql = @Cursorsql + ' WHERE (s.OfficeID = @OfficeID OR @OfficeID = 0) '; 
IF @StaffID > 0 
 SET @Cursorsql = @Cursorsql + ' AND s.StaffID = @StaffID '; 
SET @Cursorsql = @Cursorsql + ' ORDER BY s.OfficeID, s.FirstName, s.LastName ';  

exec (@Cursorsql);

Answer 2

打印出动态sql并检查语法错误总是一个好主意，也是解决问题的简单方法。在s.FirstName和s.LastName列表中缺少一个逗号

试试这个-

DECLARE 
@StaffID INT = 4,
@Cursorsql nvarchar(4000);  

SET @Cursorsql = 'DECLARE curStaff CURSOR FOR ';
SET @Cursorsql = @Cursorsql + ' SELECT s.StaffID, o.OfficeName, s.FirstName, '; 
SET @Cursorsql = @Cursorsql + ' s.LastName FROM Staff s  '; 
SET @Cursorsql = @Cursorsql + ' INNER JOIN Offices o ON o.OfficeID = s.OfficeID '; 
SET @Cursorsql = @Cursorsql + ' WHERE (s.OfficeID = @OfficeID OR @OfficeID = 0) '; 
IF @StaffID > 0 
 SET @Cursorsql = @Cursorsql + ' AND s.StaffID = @StaffID '; 
SET @Cursorsql = @Cursorsql + ' ORDER BY s.OfficeID, s.FirstName, s.LastName ';  
sp_executesql @CursorSql, N'@StaffID int', @StaffID;