Avatar of dentyne
dentyne asked on

Looping through a result set in a SQL Server stored procedure

I have a stored procedure in which I want to dynamically build a query string or order by clause and execute it.  The column names I want to select are stored in a table.  So in my stored procedure I wanted to do something like:

1.   Do a query against this lookup table and get a list of column names
2.   Loop through that list and build an order by clause.
3.   Append the order by clause to a select query, execute it, and return the result set.

I am not sure how to do this though.  In Oracle there was this cursor that allowed me to loop through the results of a select statement, but I can't figure out how to do it here and also not to return that result set.  Thanks.
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

>1.   Do a query against this lookup table and get a list of column names

declare @sql varchar(8000)
select @sql = coalesce( ',' + @sql, ' SELECT ' ) + ' [' + column_name + ']' from lookup_table

set @sql = @sql + ' FROM original_table '

>2.   Loop through that list and build an order by clause.
not sure what kind of "order by" you want to build, ie based on what information

ASKER
dentyne

Hmm I see.  So is this "select <some variable name> " the same as the "Select INTO <variable name>" from oracle?  

I am still trying to find the concept of a cursor where I can loop through the result set.  The code you gave me works, and is really slick with the coalesce function to concatenate, but how would I loop through the result set?  In Oracle you can create a cursor in the procedure and refer to it any time and loop through it row by row until the end.

Thanks in advance for your continued help.

Guy Hengel [angelIII / a3]

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
dentyne

I think I am still struggling with this.  The "fetch into" behavior of the cursor seems to put a value into one variable.  Let me try to pseudo-code what I need here:

DECLARE @select  varchar(8000)
DECLARE @fromsource varchar(8000)
DECLARE @orderby varchar(8000)

create a result set from "select column_name_formatted, source_table, column_name from build_sql"

For each record in the result set above:
      @select = @select + ',' + resultset.column_name_formatted
      @source_table = resultset.source_table  (this field repeats)
      @orderby = @orderby + ',' + resultset.column_name
end loop

set @select = @select + ' FROM ' + @source_table + ' ORDER BY ' + @orderby
exec(sql)
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dentyne

Thank you for the help Angellll.  I will be awarding you points.  Through to address the part of my original question about looping, could you explain how to do the same logic with cursors in case I need to do something more complicated?  Thanks.
Guy Hengel [angelIII / a3]

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.