SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE


Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE

Author
Message
morepainot
morepainot
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 22
I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed me that the use of CURSORs is not accepted here. I need help converting my CURSORs into FOR WHILE LOOP format. Someone please help me out. Here is the CURSOR:

CREATE PROCEDURE usp_PreStageValidation
@SQL_str1 NVARCHAR(max),
@SQL_str2 NVARCHAR(max),
@SQL_str3 NVARCHAR(max),
@SQL_str4 NVARCHAR(max),
@SQL_str5 NVARCHAR(max)
AS
BEGIN TRY
DECLARE
@Catalog nvarchar(128),
@Table nvarchar(128),
@Column nvarchar(128),
@Message nvarchar(128);

SET NOCOUNT ON




Declare @cat


Select @cat

EXEC sp_executeSQL @SQL_str1, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor1;
FETCH NEXT FROM syd_cursor1 INTO @cat,@tbl;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage table is not existed in this SOURCE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg);

FETCH NEXT FROM db_cursor1 INTO @cat,@tbl;
END;

CLOSE syd_cursor1;
DEALLOCATE syd_cursor1;



IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' ) --and this table is not existed in it
BEGIN



INSERT INTO syd_LogTable
VALUES(@cat, 'tbPlanStartEnd', NULL, 'This Table is Created Newly');
END;

--------------

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' ) --and this table is not existed in it
BEGIN


INSERT INTO syd_LogTable
VALUES(@cat, 'tbPlanGroup', NULL, 'This Table is Created Newly');
End

ELSE if
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'grp_name' ) --and this column is not existed in it
BEGIN



INSERT INTO syd_LogTable
VALUES(@cat, 'tbPlanGroup', NULL, 'This Table is added with column grp_name');
END;

END;


DECLARE syd_cursor2 CURSOR FOR
EXEC sp_executeSQL @@SQL_str2, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor2;
FETCH NEXT FROM syd_cursor2 INTO @cat,@tbl;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage table is not existed in this STAGE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg);

FETCH NEXT FROM db_cursor2 INTO @cat,@tbl;
END

CLOSE syd_cursor2;
DEALLOCATE syd_cursor2;




DECLARE syd_cursor3 CURSOR FOR
EXEC sp_executeSQL @@SQL_str3, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor3;
FETCH NEXT FROM syd_cursor3 INTO @cat,@tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage Column is not existed in this Table of this SOURCE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

FETCH NEXT FROM db_cursor3 INTO @cat,@tbl,@col;
END

CLOSE syd_cursor3;
DEALLOCATE syd_cursor3;

DECLARE syd_cursor4 CURSOR FOR
EXEC sp_executeSQL @@SQL_str4, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor4;
FETCH NEXT FROM syd_cursor4 INTO @cat,@tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage Column is not existed in this Table of this STAGE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

FETCH NEXT FROM db_cursor4 INTO @cat,@tbl,@col;
END;

CLOSE syd_cursor4;
DEALLOCATE syd_cursor4;

DECLARE syd_cursor5 CURSOR FOR
EXEC sp_executeSQL @@SQL_str5, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor5;
FETCH NEXT FROM syd_cursor5 INTO @cat,@tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

FETCH NEXT FROM db_cursor5 INTO @cat,@tbl,@col;
END

CLOSE syd_cursor5;
DEALLOCATE syd_cursor5;
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85354 Visits: 41078
morepainot (7/25/2012)
I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed me that the use of CURSORs is not accepted here. I need help converting my CURSORs into FOR WHILE LOOP format. Someone please help me out. Here is the CURSOR:


I don't have the time today to help you rewrite these but I do have time to tell you that if you're going to convert the cursors to the ol "Temp Table and While Loop", you're absolutely wasting your time. Behind the scenes, a a nice STATIC, FORWARD ONLY, READ ONLY cursor IS a Temp Table/While Loop combination behind the scenes. The conversion will do NOTHING to save on resources or duration.

To wit, this needs to be converted to SET BASED code if at all possible and I'm pretty sure that it's possible.

Also, if you really want to be respected in the world of SQL, particularly at your job, get into the habit of documenting the "WHY" of your code.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sivaj2k
sivaj2k
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 549
Hi

I am sending sample procedure to avoid cursors. Implement this logic in your procedures. This may help you.

declare @test_tab TABLE
(
[id1] [int] NULL,
query [varchar](50) NULL,
[result] [bit] NULL)

insert into @test_tab values(1,'select 30 - 50',Null)
insert into @test_tab values(2,'select 70 - 50',Null)
insert into @test_tab values(3,'select 20 - 20',Null)

select * from @test_tab
declare @start int=1,@vquery varchar(max),@result int

while @start <= (select COUNT(*) from @test_tab)
begin
select @vquery = query from @test_tab where id1 = @start
exec (@vquery)

set @start = @start + 1

end

Regard
Siva Kumar J:-)
morepainot
morepainot
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 22
Jeff and Sivaj, thank you guys for your help.

@Jeff; I understand what you said and I will keep that in mind for the next post. The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. The company I am contracted for advised me not to use them. I am a simple developer with not any serious developing experience. Doing this was a challenge for me but I believe I converted it correctly. Thank you.

Sivaj; Thanks for that. I appreciate it.
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 1789
morepainot (7/26/2012)
...The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. ...


I would love to see the tests on that statement. my question is how do you know.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
manub22
manub22
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 1863
The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.


Who said CURSOR is slower than WHILE loops? Didn't you asked them reason for the same?

While working with cursors, there are lot of options you can set to make them more performant, check the comments above by @Jeff.

Also check this blog post where I've shown CURSORS giving good performance than WHILE LOOPS: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/


~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85354 Visits: 41078
morepainot (7/26/2012)
The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.


Like I said, that's patently and absolutely not true. The WHILE LOOP is usually what makes cursor usage so slow and I've proven that many times for companies that think other wise.

The key to performance here is to get rid of the WHILE loop. Period.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25950 Visits: 17519
morepainot (7/26/2012)
Jeff and Sivaj, thank you guys for your help.

@Jeff; I understand what you said and I will keep that in mind for the next post. The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. The company I am contracted for advised me not to use them. I am a simple developer with not any serious developing experience. Doing this was a challenge for me but I believe I converted it correctly. Thank you.

Sivaj; Thanks for that. I appreciate it.


Agreed. This is nothing more than 5 insert statements. No need to loop through each row for this at all. If you really want to impress your contractor turn this into a set based solution.

The other thing about this procedure is that appears to be wide open for sql injection. You are receiving parameters and executing them. w00t

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25950 Visits: 17519
manub22 (7/26/2012)
The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.


Who said CURSOR is slower than WHILE loops? Didn't you asked them reason for the same?

While working with cursors, there are lot of options you can set to make them more performant, check the comments above by @Jeff.

Also check this blog post where I've shown CURSORS giving good performance than WHILE LOOPS: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/


Interesting blog post Manoj. It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
manub22
manub22
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 1863
It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.


Exactly @Sean, in my example there is nothing one should use a CURSOR.
This was only to show people who believe that CURSORS are evil and use WHILE loops happily. I'm not favoring CURSORS, but just want to bust that myth.

Lot of problems can be easily done by SET based approach rather than using CURSORS. People not expert in writing SQL queries and/or not aware of new features end up using CURSORS.


~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search