要插入临时表并从Temparory表中获取的SQL游标

Sir,

I have build a SQL query using Cursor as @AllRecords to insert values into Temporary Table & then Fetch values from that temporary table. But it showing me an error at last statement when I am fetching values from table (Error: incorrect syntax near @AllRecords). Below is my code:

我使用Cursor作为@AllRecords构建SQL查询,以将值插入临时表,然后从该临时表中获取值。但是当我从表中获取值时,它在最后一条语句中显示错误(错误:@AllRecords附近的语法不正确)。以下是我的代码:

DECLARE @ColName varchar(20)=null,
        @Query varchar(MAX)=null,
        @DepartmentName varchar(50)=null,
        @deptt_code varchar(4)=null,
        @DistrictId varchar(4)='0001',
        @Deptt_Id char(4)=null,
        @stYear varchar(4)=null, 
        @cYear varchar(4)=null,
        @yr varchar(9)='2017-2018',
        @tno int

BEGIN
        set @stYear = SUBSTRING(@yr,0,5)
        set @cYear = SUBSTRING(@yr,6,4)

--DECLARE & SET COUNTER
DECLARE @counter int
SET @counter = 1

--CREATE DYNAMIC TABLE WITH COLs
DECLARE @AllRecords table
(
  department_name varchar(50),
  project_name varchar(100),
  department_code varchar(4)
 )

--*** Declare Cursor
DECLARE cur_FetchDepartmentName CURSOR READ_ONLY
 FOR 
  select deptt_code,deptt_name+'('+ RTRIM(LTRIM(deptt_short))+')' as dept_name from m_Department 
  where deptt_code in (select distinct department_code from t_Project_Details where district_id=@DistrictId
  and financial_year=@yr)

OPEN cur_FetchDepartmetName

fetch next from cur_FetchDepartmetName into 
@deptt_code, @DepartmentName

--LOOP UNTIL RECORDS ARE AVAILABLE
while @@FETCH_STATUS=0
   BEGIN
      if(@tno=0)
          BEGIN
                set @tno=1
                insert into @AllRecords values(@DepartmentName,@deptt_code)
                fetch next from cur_FetchDepartmetName into
                @deptt_code,@DepartmentName
          END
      else
          BEGIN
                set @tno=@tno+1
                insert into @AllRecords values(@DepartmentName,@deptt_code)
                fetch next from cur_FetchDepartmetName into
                @deptt_code,@DepartmentName
          END

   END
        --CLOSE CURSOR
        CLOSE cur_FetchDepartmetName
        DEALLOCATE cur_FetchDepartmetName

  select department_name, department_code from @AllRecords

2 个解决方案

#1

Instead of answering what is error in this solution, I would like to offer a better solution to the problem. Use of cursor in this example is completely unnecessary, query can be more easily be written without it. It's a simple INSERT..SELECT statement and counting of records to set @tno can easily be done in the end.

我想提供一个更好的解决方案,而不是回答这个解决方案中的错误。在这个例子中使用游标是完全没必要的,没有它就可以更容易地编写查询。这是一个简单的INSERT..SELECT语句,并且可以在最后轻松完成设置@tno的记录计数。

BEGIN

set @stYear = SUBSTRING(@yr,0,5);
set @cYear = SUBSTRING(@yr,6,4);

--CREATE DYNAMIC TABLE WITH COLs
DECLARE @AllRecords table
(
  department_name varchar(50),
  project_name varchar(100), --what's the use of this column?
  department_code varchar(4)
 );

INSERT INTO @AllRecords (department_code, department_name)
select deptt_code,deptt_name+'('+ RTRIM(LTRIM(deptt_short))+')' as dept_name from m_Department 
  where deptt_code in (select distinct department_code from t_Project_Details where district_id=@DistrictId
  and financial_year=@yr);


SELECT @tNo = COALESCE(@tno,0) + COUNT(*) FROM @AllRecords;

select department_name, department_code from @AllRecords;

END

Please check this article about cursors and how to avoid them:

请查看这篇文章关于游标以及如何避免游标:

Cursors and How to Avoid Them

游标以及如何避免它们

#2

Your SQL Query as below :

您的SQL查询如下:

  BEGIN
         DECLARE @ColName VARCHAR(20)= NULL, @Query VARCHAR(MAX)= NULL, @DepartmentName VARCHAR(50)= NULL, @deptt_code VARCHAR(4)= NULL, @DistrictId VARCHAR(4)= '0001', @Deptt_Id CHAR(4)= NULL, @stYear VARCHAR(4)= NULL, @cYear VARCHAR(4)= NULL, @yr VARCHAR(9)= '2017-2018', @tno INT;
         SET @stYear = SUBSTRING(@yr, 0, 5);
         SET @cYear = SUBSTRING(@yr, 6, 4);

--DECLARE & SET COUNTER
         DECLARE @counter INT;
         SET @counter = 1;

--CREATE DYNAMIC TABLE WITH COLs
         DECLARE @AllRecords TABLE
         (department_name VARCHAR(50),
          project_name    VARCHAR(100),
          department_code VARCHAR(4)
         );

--*** Declare Cursor
         DECLARE cur_FetchDepartmentName CURSOR READ_ONLY
         FOR
             SELECT deptt_code,
                    deptt_name+'('+RTRIM(LTRIM(deptt_short))+')' AS dept_name
             FROM m_Department
             WHERE deptt_code IN
             (
                 SELECT DISTINCT
                        department_code
                 FROM t_Project_Details
                 WHERE district_id = @DistrictId
                       AND financial_year = @yr
             );
         OPEN cur_FetchDepartmetName;
         FETCH NEXT FROM cur_FetchDepartmetName INTO @deptt_code, @DepartmentName;

--LOOP UNTIL RECORDS ARE AVAILABLE
         WHILE @@FETCH_STATUS = 0
             BEGIN
                 IF(@tno = 0)
                     BEGIN
                         SET @tno = 1;
                         INSERT INTO @AllRecords
                         (department_name,
                          department_code
                         )
                                SELECT @DepartmentName,
                                       @deptt_code;
                         FETCH NEXT FROM cur_FetchDepartmetName INTO @deptt_code, @DepartmentName;
                 END;
                     ELSE
                     BEGIN
                         SET @tno = @tno + 1;
                         INSERT INTO @AllRecords
                         (department_name,
                          department_code
                         )
                                SELECT @DepartmentName,
                                       @deptt_code;
                         FETCH NEXT FROM cur_FetchDepartmetName INTO @deptt_code, @DepartmentName;
                 END;
             END;
        --CLOSE CURSOR
         CLOSE cur_FetchDepartmetName;
         DEALLOCATE cur_FetchDepartmetName;

        select department_name, department_code from @AllRecords
     END;