July 25, 2012 at 3:23 pm
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;
July 25, 2012 at 4:07 pm
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
Change is inevitable... Change for the better is not.
July 26, 2012 at 1:47 am
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:-)
July 26, 2012 at 7:39 am
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.
July 26, 2012 at 7:51 am
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 26, 2012 at 8:01 am
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/[/url]
July 26, 2012 at 8:41 am
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
Change is inevitable... Change for the better is not.
July 26, 2012 at 8:48 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 8:51 am
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/[/url]
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 8:59 am
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.
July 26, 2012 at 9:07 am
manub22 (7/26/2012)
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.
I gotcha. I just tend to side with Jeff on this topic. Why use either? They do have their place but those are very specific types of administrative tasks. In general, just don't loop or cursor. 😛
@jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing. 🙂 It is like the secret society that only members can join.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 11:24 am
I have no problem using CURSOR, as you can see by my original script. The people who have contracted have the problem with CURSOR. I dont care about using either or its just to make them happy. Im not going to go against their wishes and do what makes me happy. At the end of the day THEY sign my checks. If theyre happy then Im happy. It might take some extra time but if the work is done then its all good. Thanks for everyones input. I may have some coming in the following week or two.
July 26, 2012 at 11:31 am
morepainot (7/26/2012)
I have no problem using CURSOR, as you can see by my original script. The people who have contracted have the problem with CURSOR. I dont care about using either or its just to make them happy. Im not going to go against their wishes and do what makes me happy. At the end of the day THEY sign my checks. If theyre happy then Im happy. It might take some extra time but if the work is done then its all good. Thanks for everyones input. I may have some coming in the following week or two.
I think you missed my point. I think YOU SHOULD have an issue with using a cursor. Using a cursor or a while loop for a series of inserts is like taking a Yugo to a drag race. It will finish but it takes WAY longer than it needs to.
I don't want to get into a long drawn out discussion but I am never satisfied with "good enough".
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 6:34 pm
Sean Lange (7/26/2012)
@Jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing.
How about membership cards that actually DO mean something... especially to future employers. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2012 at 7:13 pm
Jeff Moden (7/26/2012)
Sean Lange (7/26/2012)
@Jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing.How about membership cards that actually DO mean something... especially to future employers. 😉
I do often ask about splits in the interviews and any candidate that mentions Mr Moden's splitter ends on the top of the list.
Viewing 15 posts - 1 through 15 (of 137 total)
You must be logged in to reply to this topic. Login to reply