| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2004-08-11 : 11:35:21
|
| Hi,Is it possible to call a stored procedure for every row returned from a SELECT statement?Cheers,X-Factor |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-08-11 : 11:38:12
|
| I don't think so. You may have to use a cursor :(Dustin Michaels |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-11 : 11:43:27
|
| well if you tell us what you want to do maybe you won't need a cursor.Go with the flow & have fun! Else fight the flow :) |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-08-11 : 12:52:08
|
| Well I want to do something like this...SELECT col1, (EXECUTE sp1 table1.col2) AS 'spResult'FROM table1 |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-08-11 : 13:00:53
|
| Could you convert your stored procedure to a user defined function?If you could do that then it looks like you wouldn't need a cursor |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-08-11 : 13:03:29
|
| can use a join? what is the stored procedure doing?- Jeff |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-08-11 : 13:04:42
|
| Could you convert your stored procedure to a user defined function?If you could do that then it looks like you wouldn't need a cursor |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-08-11 : 14:43:09
|
| Off the record, the undocumented extended procedure xp_execresultset will do it;exec master..xp_execresultset N'select ''exec master..sp_who2 '''''' + name + '''''''' from master..syslogins',N'pubs'That executes: select 'exec master..sp_who2 ''' + name + '''' from master..sysloginswhich generates a bunch of statements like:exec master..sp_who2 'BUILTIN\Administrators'exec master..sp_who2 'BUILTIN\Users'exec master..sp_who2 'BUILTIN\Power Users'...then it executes each one in turn. But it's probably better to build your own.--Ken"Knowledge is a process of piling up facts; wisdom lies in their simplification." |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-08-11 : 15:21:15
|
| The sp is taking several values form the record being selected and doing a query based on them and then analysing the result and finally returning an integer status code. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-08-11 : 15:29:51
|
| Post what your stored procedure does, there may be a way to do everything with one query.Dustin Michaels |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-08-11 : 15:30:24
|
| Oh, actually I forgot something.The sp is taking several out parameters and I wouldn't mind having these in the select list too. |
|
|
|
|
SP on every row