Sir,
I have build a cursor to fetch details from Table available in database and save those values in temporary table. But there is an error be an issue at last when need to Select data from temporary table. Error is (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'
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
insert into @AllRecords(
department_name,project_name
)
--select pd.department_code from t_Project_Details pd
values(@DepartmentName,@deptt_code)
fetch next from cur_FetchDepartmetName into
@deptt_code,@DepartmentName
END
--CLOSE CURSOR
CLOSE cur_FetchDepartmetName
DEALLOCATE cur_FetchDepartmetName
select * from @AllRecords