| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2002-05-28 : 13:39:56
|
| I want to do something like this. Anyone know of a way to make such a thing work?SELECT u.LastName, (exec mystoredproc @bFinished = @myOutputVar, @Userid = u.UsierID) FROM users uI want to return the LastName, and the output param for each "user".Any ideas?Michael |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-28 : 14:37:31
|
| I don't believe there is a nifty trick for this one. You'll need to find another way, such as iterating through your users and capturing the results of the proc into a temp table, then joining the temp table back to users for you final resultset. Depending on the logic in mystoredproc, you may be able to do its processing on the entire set of users, but its not clear for here . . .can you post mystoredproc?<O> |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-28 : 14:42:22
|
| I'm working on a User Defined function as a solution to this problem. I'm pretty sure it's going to work.The biggest problem is that i'm returning 2 values out of the UDF. this should be interesting. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-28 : 14:54:45
|
quote:
The biggest problem is that i'm returning 2 values out of the UDF.
That's really not possible . . . maybe you should look at a table-valued user-defined function and have the two values be two columns in a table.<O> |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-28 : 15:02:52
|
| Yep, exactly what I'm doing now, but for some reason it keeps telling me the UDF doesn't exist in the database.The UDF syntax checks ok, I'm in the right DB, the UDF is in the right DB, I've tried dbo.functionname and rthat doesn't seem to help.I'm really starting to pull my hair out on this one.Michael |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-28 : 15:05:29
|
| Ahh, found the problem! It helps if I read the entire BOL article.If the RETURNS clause specifies a TABLE type with columns and their data types, the function is a multi-statement table-valued function.The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: Assignment statements.Control-of-Flow statements.DECLARE statements defining data variables and cursors that are local to the function.SELECT statements containing select lists with expressions that assign values to variables that are local to the function.Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.INSERT, UPDATE, and DELETE statements modifying table variables local to the function.EXECUTE statements calling an extended stored procedures |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-28 : 15:46:16
|
| Well i can't call stored procs when returning a table......So it looks like I'm going to have to try something I've neer used before! CURSORS!!Be afraid...be very afriad.Michael |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-05-28 : 16:12:40
|
quote:
I'm working on a User Defined function as a solution to this problem. I'm pretty sure it's going to work.The biggest problem is that i'm returning 2 values out of the UDF. this should be interesting.
Why not two user defined functions? One for each value.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-29 : 10:07:31
|
| Well that's what I thought too, but it's not going to work that way either because of the stuff I have to return.The cursor solution seems to be working fine, and this application doesn't need to be a high performance app. There's only going to be sub five users, and usually only 1-2 logged in at once. Maybe I'll revist this thing later on and re-try the two UDF solution.Michael |
|
|
|
|
Output Params in a Select Statement