How do you LOOP in T-SQL? : The Official Microsoft ASP.NET Forums

Reply

How do you LOOP in T-SQL?

Aug 20, 2007 03:48 PM|LINK

I used to loop through recordsets with ease in old classic .asp pages.

Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.

I would love to learn how to do it to better develop my skills.

SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]


IF [Product Interest - Actuators] IS NOT NULL THEN
	UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]


IF [Product Interest - Other Actuator] IS NOT NULL THEN
	UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]


IF [Product Interest - Other Chillers] IS NOT NULL THEN
	UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]

Edited by SolitaryMan on Aug 20, 2007 04:57 PM

Reply

Re: How do you LOOP in T-SQL?

Aug 20, 2007 03:53 PM|LINK

Here is a nice article on T-SQL looping.

http://www.databasejournal.com/features/mssql/article.php/3100621

James Hearst
.NET Application Developer

Reply

Re: How do you LOOP in T-SQL?

Aug 20, 2007 03:58 PM|LINK

Hi thank you for your reply.

Is that the Definative way to do it?

Or was that just a search result you got using a search engine?

I am hoping more for an expert example. Sometimes articles can be "close" but not quite the "way".

FYI: I am using SQL Server 2000

I notice in the article they are doing a set number of loops, I.e. while @counter < 10 etc.. I would like to loop through the select statement but would have not idea of the number of records..

Edited by SolitaryMan on Aug 20, 2007 04:01 PM
Edited by SolitaryMan on Aug 20, 2007 04:02 PM

Reply

Re: How do you LOOP in T-SQL?

Aug 20, 2007 04:08 PM|LINK

If you are trying to loop through a result set use a cursor.

http://www.sql-server-performance.com/articles/per/operations_no_cursors_p1.aspx

Note: Cursors will have a significant preformace impact on your query, especially if you have more then a couple hundred records.

James Hearst
.NET Application Developer

Marked as answer by SolitaryMan on Aug 20, 2007 04:55 PM

Reply

Re: How do you LOOP in T-SQL?

Aug 20, 2007 04:19 PM|LINK

One way to do this would be to use a CURSOR. Now let me warn you before you do use a cursor is that there can be some performance hits when using a cursor. It all depends upon how big your returning record set is and how many concurrent connections you have to this stored procedure that is running the record set. But 1st to answer your question, to do loops in T-SQL you can do a While Loop which the link provided is a good basic example for you, or you can use a cursor. What a cursor does essentially is created a result set of data that you can then loop through in SQL.

Here is an example of a simple cursor that performs a series of updates.

Declare @productOtherActuator varchar(50), @productActuator varchar(50)


DECLARE temp_cursor CURSOR
FOR SELECT [Product Interest - Other Actuator], [Product Interest - Actuator] FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]


OPEN temp_cursor


FETCH NEXT FROM temp_cursor
INTO @productOtherActuator, @productActuator


WHILE @@FETCH_STATUS = 0
BEGIN


IF [@productActuator ] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]


IF [@productOtherActuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]


FETCH NEXT FROM temp_cursor
INTO @productOtherActuator, @productActuator


END


CLOSE temp_cursor
DEALLOCATE temp_cursor

Couple of key notes here.

You must include your FETCH NEXT inside your loop to actually move the cursor to the next record.

Always Close and Deallocate your cursor. Doing this will remove the cursor from memory in your SQL server

and the number of columns selected in your select statement must match the number of columns in your fetch next statement.

Dave Yancey
My Blog

Edited by Diamsorn on Aug 20, 2007 04:20 PM
Marked as answer by SolitaryMan on Aug 20, 2007 04:55 PM

Reply

ndinakar

All-Star

49092 Points

6868 Posts

Moderator

MVP

Re: How do you LOOP in T-SQL?

Aug 20, 2007 05:18 PM|LINK

I would advise strong caution before using CURSORs. Check this for sample code on how to loop without using cursors: http://forums.asp.net/t/1146134.aspx

Google for cursors and you will see a lot of articles on why not to use them and when you have no choice but to use them. Personally, Triggers is a no-no for me.

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************

Marked as answer by SolitaryMan on Aug 20, 2007 05:24 PM

Reply

SolitaryMan

Member

154 Points

275 Posts

Re: How do you LOOP in T-SQL?

Aug 20, 2007 05:26 PM|LINK

Ok, understood. I will definately take from this lesson, NOT to see CURSORS as a common solution.

Since I am in a situation where I am pretty much doing "ONE-OFF'S" I will go through this CURSOR excercise.

But I will not promote them to "PRODUCTION" at this time. I will simply use it as a means to update some dataTables.

Reply

SolitaryMan

Member

154 Points

275 Posts

Re: How do you LOOP in T-SQL?

Aug 20, 2007 05:55 PM|LINK

I think I might have run into a road-block. Because, does this approach allow me to select the [CONTACT NAME] field as well? I have added two conditional LIKE where clauses just to weed out a couple data errors. I have not other choice but to use the contact name as the unique field ( :( they should have BA's take RI courses in school )...

I also want to limit the search possibilities in the destination table so I added AND Trade_Show_Id = 31 in the UPDATE clauses.

ERROR MESSAGE:

Server: Msg 207, Level 16, State 3, Procedure ap_OneOff_Product_Interest_Semicon_2007, Line 31
Invalid column name '@productActuators'.
Server: Msg 207, Level 16, State 1, Procedure ap_OneOff_Product_Interest_Semicon_2007, Line 32
Invalid column name 'Contact Name'.
Server: Msg 207, Level 16, State 1, Procedure ap_OneOff_Product_Interest_Semicon_2007, Line 34
Invalid column name '@productOtherActuator'.
Server: Msg 207, Level 16, State 1, Procedure ap_OneOff_Product_Interest_Semicon_2007, Line 35
Invalid column name 'Contact Name'.

SP:

ALTER PROCEDURE dbo.ap_OneOff_Product_Interest_Semicon_2007
AS


DECLARE @productActuators varchar(255), @productOtherActuator varchar(255)
-- ,@productChillers varchar(255), @productOtherChiller varchar(255)
-- ,@productElectronic varchar(255), @productOtherNetworkI varchar(255)
-- ,@productFittings varchar(255), @productHighVac varchar(255)
-- ,@productOtherHighVac varchar(255), @productPickPlaceTransfer varchar(255)
-- ,@productTeflon varchar(255), @productTraining varchar(255)
-- ,@productValvesManifolds varchar(255)


-- A cursor will accept commands for processing each row independently, rather than processing the whole result set.
DECLARE temp_cursor CURSOR


FOR SELECT [Product Interest - Actuators], [Product Interest - Other Actuator] FROM zz_Semicon_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Actuators], [Product Interest - Other Actuator], [Product Interest - Chillers], [Product Interest - Other Chiller],
[Product Interest - Electronic Products], [Product Interest - Other network interfaces], [Product Interest - Fittings],
[Product Interest - High Vacuum], [Product Interest - Other high vacuum actuators], [Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products], [Product Interest - Training], [Product Interest - Valves& Manifolds]) Is Not Null
AND [Contact Name] <> '' AND [Contact Name] NOT LIKE '%|%'
Order BY [Contact Name]


OPEN temp_cursor


FETCH NEXT FROM temp_cursor
INTO @productActuators, @productOtherActuator


WHILE @@FETCH_STATUS = 0
BEGIN
IF [@productActuators] IS NOT NULL
UPDATE Booth_Visitors_Data Set Act_Pnuematic = 1 Where [Contact Name] = Contact_Name AND Trade_Show_Id = 31


IF [@productOtherActuator] IS NOT NULL
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = Contact_Name AND Trade_Show_Id = 31


FETCH NEXT FROM temp_cursor
INTO @productActuators, @productOtherActuator


END


CLOSE temp_cursor
DEALLOCATE temp_cursor


-- WORKING SELECT STATEMENT
-- SELECT * FROM zz_Semicon_2007_Booth_Visitors
-- WHERE COALESCE ([Product Interest - Other Actuator], [Product Interest - Chillers], [Product Interest - Other Chiller],
-- [Product Interest - Electronic Products], [Product Interest - Other network interfaces], [Product Interest - Fittings],
-- [Product Interest - High Vacuum], [Product Interest - Other high vacuum actuators], [Product Interest - Pick& Place and Transfer],
-- [Product Interest - Teflon Products], [Product Interest - Training], [Product Interest - Valves& Manifolds], [Product Interest - Actuators]) Is Not Null
-- AND [Contact Name] <> '' AND [Contact Name] NOT LIKE '%|%'
-- Order BY [Contact Name]
--
GO

FYI: A popular book on the subject [SOURCE CITED*] states: "

Creating a Cursor

Cursors work in multiple statement SQL scripts where the cursor provides a row of data to the remaining statements in the script. Because of this, the best places to use cursor processing are in SQL scripts, stored procedures, and triggers. The latter two will be discussed later in this book.

In order to use cursors—meaning to create, use, and destroy cursors—you must perform a series of commands in a particular order. These commands are

<div style="FONT-WEIGHT: normal">
Define T-SQL variables to contain the data returned by the cursor.
</div>
<div style="FONT-WEIGHT: normal">
Define the cursor by associating it with a SELECT statement.
</div>
<div style="FONT-WEIGHT: normal">
Open the cursor.
</div>
<div style="FONT-WEIGHT: normal">
Use the FETCH INTO statement to retrieve individual rows of data.
</div>
<div style="FONT-WEIGHT: normal">
Close the cursor.
</div>
<div style="FONT-WEIGHT: normal">
Deallocate the cursor.
</div>

</div>

If you take a close look at these steps, you can see the basic structure of most computer programs. That is, define memory, connect to the data, request data, close the connection, and release memory."

Is it Possible my WHERE clauses are breaking the code?

Can I add the columns CONTACT_NAME and TRADE_SHOW_ID to the select statement?

* Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Edited by SolitaryMan on Aug 20, 2007 05:56 PM
Edited by SolitaryMan on Aug 20, 2007 05:57 PM
Edited by SolitaryMan on Aug 20, 2007 05:57 PM
Edited by SolitaryMan on Aug 20, 2007 05:58 PM
Edited by SolitaryMan on Aug 20, 2007 06:08 PM
Edited by SolitaryMan on Aug 20, 2007 06:09 PM
Edited by SolitaryMan on Aug 20, 2007 06:10 PM
Edited by SolitaryMan on Aug 20, 2007 06:17 PM
Edited by SolitaryMan on Aug 20, 2007 06:18 PM

Reply

ndinakar

All-Star

49092 Points

6868 Posts

Moderator

MVP

Re: How do you LOOP in T-SQL?

Aug 20, 2007 06:21 PM|LINK

From a quick look, I can see an issue with your UPDATE statement.

Where [Contact Name] = Contact_Name

Where is this Contact_Name column/value coming from that you are comparing against here? Is it in the same table zz_Semicon_2007_Booth_Visitors ?

Where are you using the 2 values that you got into the variables here? Your UPDATE will update the entire table for matching rows in the WHERE condition.

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************

Edited by ndinakar on Aug 20, 2007 06:22 PM
Edited by ndinakar on Aug 20, 2007 06:23 PM
Marked as answer by SolitaryMan on Aug 20, 2007 06:34 PM

Reply

SolitaryMan

Member

154 Points

275 Posts

Re: How do you LOOP in T-SQL?

Aug 20, 2007 06:25 PM|LINK

Yes. The [Contact Name] in zz_Semicon_2007_Booth_Visitors should be compared with Contact_Name and Trade_Show_Id when updating the destination table.

I have been trying to grab it like:

DECLARE temp_cursor CURSOR

FOR SELECT [Product Interest - Actuators], [Product Interest - Other Actuator], [Contact Name] As cName FROM zz_Semicon_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Actuators], [Product Interest - Other Actuator], [Product Interest - Chillers], [Product Interest - Other Chiller],
[Product Interest - Electronic Products], [Product Interest - Other network interfaces], [Product Interest - Fittings],
[Product Interest - High Vacuum], [Product Interest - Other high vacuum actuators], [Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products], [Product Interest - Training], [Product Interest - Valves& Manifolds]) Is Not Null
AND [Contact Name] <> '' AND [Contact Name] NOT LIKE '%|%'
Order BY [Contact Name]

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @productActuators, @productOtherActuator

WHILE @@FETCH_STATUS = 0
BEGIN
IF [@productActuators] IS NOT NULL
UPDATE Booth_Visitors_Data Set Act_Pnuematic = 1 Where Contact_Name = cName AND Trade_Show_Id = 31

IF [@productOtherActuator] IS NOT NULL
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where Contact_Name = cName AND Trade_Show_Id = 31

FETCH NEXT FROM temp_cursor
INTO @productActuators, @productOtherActuator

END

CLOSE temp_cursor
DEALLOCATE temp_cursor

How do you LOOP in T-SQL? RSS

24 replies

SolitaryMan

How do you LOOP in T-SQL?

bluemistonli...

Re: How do you LOOP in T-SQL?

SolitaryMan

Re: How do you LOOP in T-SQL?

bluemistonli...

Re: How do you LOOP in T-SQL?

Diamsorn

Re: How do you LOOP in T-SQL?

ndinakar

Re: How do you LOOP in T-SQL?

SolitaryMan

Re: How do you LOOP in T-SQL?

SolitaryMan

Re: How do you LOOP in T-SQL?

Creating a Cursor

ndinakar

Re: How do you LOOP in T-SQL?

SolitaryMan

Re: How do you LOOP in T-SQL?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft