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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Query Help- Cursor change into FOR WHILE...

138 posts, Page 1 of 14

1 2 3 4 5 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

morepainot

Posted Wednesday, July 25, 2012 3:23 PM

Valued Member

Group: General Forum Members
Last Login: Friday, August 10, 2012 7:32 AM
Points: 57, 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;

Post #1335448

Jeff Moden

Posted Wednesday, July 25, 2012 4:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792

morepainot (7/25/2012)

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1335473

sivaj2k

Posted Thursday, July 26, 2012 1:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 2:46 AM
Points: 45, Visits: 420

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 Smile

Post #1335600

morepainot

Posted Thursday, July 26, 2012 7:39 AM

Valued Member

Group: General Forum Members
Last Login: Friday, August 10, 2012 7:32 AM
Points: 57, 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.

Post #1335846

CapnHector

Posted Thursday, July 26, 2012 7:51 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

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

Jeremy Oursler

Post #1335851

manub22

Posted Thursday, July 26, 2012 8:01 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 82, Visits: 558

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
sqlwithmanoj.wordpress.com

Post #1335857

Jeff Moden

Posted Thursday, July 26, 2012 8:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792

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

Post #1335893

Sean Lange

Posted Thursday, July 26, 2012 8:48 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

morepainot (7/26/2012)

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1335901

Sean Lange

Posted Thursday, July 26, 2012 8:51 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

manub22 (7/26/2012)

The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1335906

manub22

Posted Thursday, July 26, 2012 8:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 82, Visits: 558

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
sqlwithmanoj.wordpress.com

Post #1335915

« Prev Topic | Next Topic »

138 posts, Page 1 of 14

1 2 3 4 5 »»»

Permissions