Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2005-12-06 : 18:19:08
|
| In a sProc I want to get a recordset, modify some values based on logic that's too complex for cramming it all into a SELECT statement, and return the modified recordset. In a procedural language, I would fill a rowset with data, loop through it and modify values, and return it. How is that best accomplished in SS2k?My slant is doing this within the same sProc or UDF. In other words, I would like to loop through what SELECT returns. But maybe I should do it some other way (temp table, table variables?) |
|
|
jhermiz
3564 Posts |
Posted - 2005-12-06 : 22:19:42
|
Post sample data, and expected results, or at least post what exactly you are trying to do. Something tells me this can be set based and no looping / cursors are required.Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url] |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-06 : 22:29:12
|
quote:
In a procedural language, I would fill a rowset with data, loop through it and modify values, and return it. How is that best accomplished in SS2k?
By NOT doing it as you would in a procedural language.As Jon said, we need examples of data, structure, and results.Here are some examples of passing multiple rows' worth of data to a proc and processing it set-based:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19565http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13935You can also pass XML and use OPENXML to parse it. SQL Server also has "updategrams" that can update relational tables from XML data, you can find some more about it here:http://support.microsoft.com/default.aspx?scid=kb;en-us;316018 |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-12-07 : 19:28:19
|
quote:
Originally posted by robvolkBy NOT doing it as you would in a procedural language.
Appreciate your links and articles. I started a new thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58778 which better explains what we are trying to do. Regarding my question in this thread, look in my sProc code where I do EXEC('SELECT * FROM ['+@sTableName+']'). Here is where I would like to read records @sTableName one by one in a loop and:- Validate them- Create the main record in the real table- Create records in real linking tables- Possibly create a new recordset based on the temp table and return it |
|
|
|
|
|
|
|
|
Looping through recordset inside sProc