Can a FOR/NEXT type loop be done in a SQL Stored Procedure

I need to code a for next loop in a SQL stored procedure.  I tried joining multiple SELECT statements together but the length of the string is greater then 8000.   Basically the SELECT statement returns the same 3 values, only the where clause changes.  Looking for a value equal to an specific number that is then incremented 10 times, ie WHERE ItemCount = 10,  WHERE ItemCount = 11 etc.  Looking to loop and increcment the value.  I am assignng the results to a temp table and then retreiveing them at the end of the process.

If it is possible what is the syntax?

Thank you
skinsfan99Asked:
Who is Participating?
 
cmgarnettCommented:
DECLARE @counter INT

SET @counter = 1

WHILE @counter <= 10
BEGIN

SELECT A, B, C FROM MyTable WHERE ItemCount= @counter

SET @counter = (@counter + 1)

END
0
 
wls3Commented:
Here is an approach I use all the time now:

http://learningpcs.blogspot.com/2010/02/tsql-rowcount.html

As outlined in the link, you use @@ROWCOUNT immediately after your select statement to gather the data.  Also, in 2008, you can join multiple lines with EXEC(@Data1 + @Data2 + @Data3 + ... + @Data10).  But, even easier is VARCHAR(MAX):

http://msdn.microsoft.com/en-us/library/ms178158.aspx
0
 
skinsfan99Author Commented:
Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.