problem in temp table in a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem in temp table in a cursor

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2011-10-08 : 03:57:05

When adding temp table inside a cursor showing error that the table already exists, please have a look at the below script and let me help to solve the issue..
begin
DECLARE @Process2ReportId int, @Technology_Nbr nvarchar(50)

DECLARE TechnologyNo_cursor CURSOR FOR
SELECT Process2ReportId, Technology_Nbr
FROM Process2Report

OPEN TechnologyNo_cursor;

FETCH NEXT FROM TechnologyNo_cursor
INTO @Process2ReportId, @Technology_Nbr;

WHILE @@FETCH_STATUS = 0
BEGIN
declare @count int
declare @licensed varchar(20)
set @licensed='Not Licensed'
declare @LicenseNumber as varchar(20)
IF OBJECT_ID('DCS.#tmp1') IS NOT NULL
DROP TABLE #tmp1
IF OBJECT_ID('DCS.#tmp2') IS NOT NULL
DROP TABLE #tmp2

SELECT @count [Agrisure Signed Ind/Grower License Number] INTO #tmp1
FROM [dbo].[MasterFile]
WHERE ([Agrisure Signed Ind/Grower License Number] LIKE @Technology_Nbr+'%') OR [Agrisure Signed Ind/Grower License Number] =@Technology_Nbr
select @count=count(*) from #tmp1
if(@count=0)

begin
SELECT @count [Agrisure Signed Ind/Grower License Number] INTO #tmp2
FROM [dbo].[MasterFile]
WHERE [Customer # / SAP ID] =@Technology_Nbr
select @count=count(*) from #tmp2
if(@count<>0)
begin

select top(1) @LicenseNumber= [Agrisure Signed Ind/Grower License Number] from #tmp2 where [Agrisure Signed Ind/Grower License Number] Like('%02')
if(@LicenseNumber<>'')
begin
set @licensed='Licensed'
end
else
begin
select top(1) @LicenseNumber= [Agrisure Signed Ind/Grower License Number] from #tmp2 where [Agrisure Signed Ind/Grower License Number] Like('%01')
if(@LicenseNumber<>'')
set @licensed='Under Licensed'
end

end
end
else
begin

select top(1) @LicenseNumber= [Agrisure Signed Ind/Grower License Number] from #tmp1 where [Agrisure Signed Ind/Grower License Number] Like('%02')
if(@LicenseNumber<>'')
begin
set @licensed='Licensed'
end
else
begin
select top(1) @LicenseNumber= [Agrisure Signed Ind/Grower License Number] from #tmp1 where [Agrisure Signed Ind/Grower License Number] Like('%01')
if(@LicenseNumber<>'')
set @licensed='Under Licensed'
end

end

Update Process2Report set Licensed=@licensed where Process2ReportId=@Process2ReportId

FETCH NEXT FROM TechnologyNo_cursor
INTO @Process2ReportId, @Technology_Nbr;
END
CLOSE TechnologyNo_cursor;
DEALLOCATE TechnologyNo_cursor;

UPDATE [MasterFileFiles] SET [Processed] =1
set @result=1
end

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 04:27:13

I think you just need this. no need of cursor at all


UPDATE pr1
SET pr1.Licensed= CASE WHEN t.Cat = 1 THEN 'Licensed' WHEN t.Cat=2 THEN 'Under Licensed' END
FROM Process2Report pr1
INNER JOIN
(
SELECT Process2ReportId,CASE WHEN [Agrisure Signed Ind/Grower License Number] Like('%02') THEN 1 WHEN [Agrisure Signed Ind/Grower License Number] Like('%01') THEN 2 ELSE 3 END AS Cat,
ROW_NUMBER() OVER (PARTITION BY  Process2ReportId ORDER BY CASE WHEN m.[Agrisure Signed Ind/Grower License Number] LIKE pr.Technology_Nbr+'%' THEN 1 WHEN m.[Customer # / SAP ID] =pr.Technology_Nbr THEN 2 ELSE 3 END ASC,CASE WHEN [Agrisure Signed Ind/Grower License Number] Like('%02') THEN 1 WHEN [Agrisure Signed Ind/Grower License Number] Like('%01') THEN 2 ELSE 3 END) AS Rn
FROM Process2Report pr
LEFT JOIN [dbo].[MasterFile] m
ON ((m.[Agrisure Signed Ind/Grower License Number] LIKE pr.Technology_Nbr+'%')
OR (m.[Customer # / SAP ID] =pr.Technology_Nbr ))
)t
ON t.Process2ReportId = pr1.Process2ReportId
AND t.Rn=1



UPDATE [MasterFileFiles] SET [Processed] =1
set @result=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-08 : 10:20:04

Hi,

Please avoid cursor as it detrioit performance, rather use update with inner join to achive your desire results.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 11:14:16

quote:
Originally posted by jassi.singh

Hi,

Please avoid cursor as it detrioit performance, rather use update with inner join to achive your desire results.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

which is exactly what i've shown in my suggestion above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources