SQL Cursor to Insert into Temporary Table and Fetch From Temparory Table

Question

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:

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

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

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