Cursor-based into Set-based? - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Cursor-based into Set-based?

Author

Topic

mfellahi
Starting Member

3 Posts

Posted - 2012-01-06 : 15:41:43

I know many people hate cursor-based solutions. I have one, and for all of you gurus, please explain to me how it can be transformed into a set-based query. Here goes nothing:

IF (SELECT CURSOR_STATUS('global','crsRepCodes')) >=0
BEGIN
close crsRepCodes
DEALLOCATE crsRepCodes
END

declare crsRepCodes cursor for
select code_princ, nom_rep_princ, status, date_entry, platform, rep_code_2, rep_code_3,
rep_code_4, rep_code_5, rep_code_6, rep_code_7, rep_code_8, rep_code_9, rep_code_10
from #rep_codes_test
order by code_princ

open crsRepCodes

fetch next from crsRepCodes
into @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, @strPlatform, @strRep2,
@strRep3, @strRep4, @strRep5, @strRep6, @strRep7, @strRep8, @strRep9, @strRep10

set @blnNoRep = 1

WHILE @@FETCH_STATUS = 0
begin

if SUBSTRING(@strRep2,5,1) = '-'
set @strRep2 = SUBSTRING(@strRep2,1,4) + SUBSTRING(@strRep2,6,4)
else
set @strRep2 = SUBSTRING(@strRep2,1,8)

if SUBSTRING(@strRep3,5,1) = '-'
set @strRep3 = SUBSTRING(@strRep3,1,4) + SUBSTRING(@strRep3,6,4)
else
set @strRep3 = SUBSTRING(@strRep3,1,8)

if SUBSTRING(@strRep4,5,1) = '-'
set @strRep4 = SUBSTRING(@strRep4,1,4) + SUBSTRING(@strRep4,6,4)
else
set @strRep4 = SUBSTRING(@strRep4,1,8)

if SUBSTRING(@strRep5,5,1) = '-'
set @strRep5 = SUBSTRING(@strRep5,1,4) + SUBSTRING(@strRep5,6,4)
else
set @strRep5 = SUBSTRING(@strRep5,1,8)

if SUBSTRING(@strRep6,5,1) = '-'
set @strRep6 = SUBSTRING(@strRep6,1,4) + SUBSTRING(@strRep6,6,4)
else
set @strRep6 = SUBSTRING(@strRep6,1,8)

if SUBSTRING(@strRep7,5,1) = '-'
set @strRep7 = SUBSTRING(@strRep7,1,4) + SUBSTRING(@strRep7,6,4)
else
set @strRep7 = SUBSTRING(@strRep7,1,8)

if SUBSTRING(@strRep8,5,1) = '-'
set @strRep8 = SUBSTRING(@strRep8,1,4) + SUBSTRING(@strRep8,6,4)
else
set @strRep8 = SUBSTRING(@strRep8,1,8)

if SUBSTRING(@strRep9,5,1) = '-'
set @strRep9 = SUBSTRING(@strRep9,1,4) + SUBSTRING(@strRep9,6,4)
else
set @strRep9 = SUBSTRING(@strRep9,1,8)

if SUBSTRING(@strRep10,5,1) = '-'
set @strRep10 = SUBSTRING(@strRep10,1,4) + SUBSTRING(@strRep10,6,4)
else
set @strRep10 = SUBSTRING(@strRep10,1,8)

if @strRep2 <> ''
begin
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep2, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end

if @strRep3 <> ''
begin
if (@strRep3 <> @strRep2)
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep3, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep4 <> ''
begin
if ((@strRep4 <> @strRep3) and (@strRep4 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep4, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep5 <> ''
begin
if ((@strRep5 <> @strRep4) and (@strRep5 <> @strRep3) and
(@strRep5 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep5, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep6 <> ''
begin
if ((@strRep6 <> @strRep5) and (@strRep6 <> @strRep4) and
(@strRep6 <> @strRep3) and (@strRep6 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep6, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep7 <> ''
begin
if ((@strRep7 <> @strRep6) and (@strRep7 <> @strRep5) and
(@strRep7 <> @strRep4) and (@strRep7 <> @strRep3) and
(@strRep7 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep7, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep8 <> ''
begin
if ((@strRep8 <> @strRep7) and (@strRep8 <> @strRep6) and
(@strRep8 <> @strRep5) and (@strRep8 <> @strRep4) and
(@strRep8 <> @strRep3) and (@strRep8 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep8, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep9 <> ''
begin
if ((@strRep9 <> @strRep8) and (@strRep9 <> @strRep7) and
(@strRep9 <> @strRep6) and (@strRep9 <> @strRep5) and
(@strRep9 <> @strRep4) and (@strRep9 <> @strRep3) and
(@strRep9 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep9, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @strRep10 <> ''
begin
if ((@strRep10 <> @strRep9) and (@strRep10 <> @strRep8) and
(@strRep10 <> @strRep7) and (@strRep10 <> @strRep6) and
(@strRep10 <> @strRep5) and (@strRep10 <> @strRep4) and
(@strRep10 <> @strRep3) and (@strRep10 <> @strRep2))
begin
set @blnNoRep = 0
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strRep10, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end
end

if @blnNoRep = 1
begin
INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
VALUES(@strCodePrinc, @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, NULL, '', @strPlatform)
end

fetch next from crsRepCodes
into @strCodePrinc, @strNomPrinc, @strStatus, @datDateEntry, @strPlatform, @strRep2,
@strRep3, @strRep4, @strRep5, @strRep6, @strRep7, @strRep8, @strRep9, @strRep10

end

GO

close crsRepCodes
deallocate crsRepCodes

What this query does is take a table with 10 fields, and based upon there is a value in the field, it updates it by inserting one record into another table. To complicate matters further, there is a possibiliy that the value has already been inserted, so it cannot be duplicated.

I don't particularly like the cursor option as I find it slow. I
have another process that I will post that kills me with the amount of time it takes to run. But I'll start with this one. Thank you in advance for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-06 : 15:48:15

Why don't you start with the Query that creates the mess that is your temp table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mfellahi
Starting Member

3 Posts

Posted - 2012-01-07 : 13:15:28

INSERT INTO #rep_codes_test
select rep_code_primary_c, (last_name + ' ' + first_name), 'A', getdate(), 'V',
rep_code_2_c, rep_code_3_c, rep_code_4_c, rep_code_5_c, rep_code_6_c,
rep_code_7_c, rep_code_8_c, rep_code_9_c, rep_code_10_c
from [PEAK-SQLEWMS01\UNIVERIS7717].PEAKOnlineUSTest.dbo.Representant
LEFT JOIN [PEAK-SQLEWMS01\UNIVERIS7717].crm.dbo.contacts_cstm
ON ((Representant.Agent_ID = contacts_cstm.rep_code_primary_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_2_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_3_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_4_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_5_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_6_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_7_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_8_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_9_c) or
(Representant.Agent_ID = contacts_cstm.rep_code_10_c))
LEFT JOIN [PEAK-SQLEWMS01\UNIVERIS7717].crm.dbo.contacts
ON (contacts.id = contacts_cstm.id_c)
where ((left(Agent_Id,4) not in ('7682','3501')) and
(Agent_Id <> '96200098' and Agent_Id <> '96202273' and Agent_Id <> '96202338' and
Agent_Id <> '96202600' and Agent_Id <> '96203000' and Agent_Id <> '96203003' and
Agent_Id <> '96206200' and Agent_Id <> '99999998' and Agent_Id <> '99999999') and
(Agent_Id <> '76368774' and Agent_Id <> '76368967' and Agent_Id <> '76368994' and
Agent_Id <> '96202029' and Agent_Id <> '96202051' and Agent_Id <> '96202079' and
Agent_Id <> '96202223' and Agent_Id <> '96202450' and Agent_Id <> '96208823'))
order by Agent_ID
GO

Here you go...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 03:22:30

i think your original query is equivalent to this


INSERT INTO [PEAK-GGSQL01].PEAKOnlineUSTest.dbo.rep_codes
select distinct COALESCE(NULLIF(val,'NULL'),code_princ),code_princ, nom_rep_princ, status, date_entry,NULL,'', platform
from
(
select code_princ, nom_rep_princ, status, date_entry, platform, COALECSE(rep_code_2,'NULL') AS rep_code_2, COALECSE(rep_code_3,'NULL') AS rep_code_3, 
COALECSE(rep_code_4,'NULL') AS rep_code_4, COALECSE(rep_code_5,'NULL') AS rep_code_5, COALECSE(rep_code_6,'NULL') AS rep_code_6, 
COALECSE(rep_code_7,'NULL') AS rep_code_7, COALECSE(rep_code_8,'NULL') AS rep_code_8, COALECSE(rep_code_9,'NULL') AS rep_code_9, 
COALECSE(rep_code_10,'NULL') AS rep_code_10
from #rep_codes_test)m
unpivot(val for rep_code in(rep_code_2, rep_code_3, 
rep_code_4, rep_code_5, rep_code_6, rep_code_7, rep_code_8, rep_code_9, rep_code_10))u 
order by code_princ

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

edit: fixed long lines

mfellahi
Starting Member

3 Posts

Posted - 2012-01-10 : 10:44:55

Thanks. I'll try that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 10:47:13

welcome
let us know how you got on

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources