| Author |
Topic |
|
LamboG
Starting Member
4 Posts |
 Posted - 2009-02-09 : 07:21:54
|
| Trying to store the results from a stored procedure into a variable, which will later be used to insert into a table.DECLARE @Body varchar(50)EXEC @Body = dbo.usp_GetSupplyUtil '1A2345'SELECT Body dbo.usp_GetSupplyUtil '1A2345' does give me the correct results but doesn't store in @Body |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-09 : 07:29:22
|
| LamboG:This isn't how stored procs work.The return value is the state of the stored proc. (usually 0 if no error has occured).If you need the results produced via a SELECT in your stored proc you will have to:1) Set up a temp table and do an INSERT EXEC2) If the stored proc returns only a single value (which it seems like it does), change the stored proc to a FUNCTION and useSET @body = dbo.myFunction('1A2345')What does your procedure do?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-02-09 : 07:31:17
|
| U can directly insert into the table INSERT INTO urtable(fieldName)EXEC dbo.usp_GetSupplyUtil '1A2345'Jai Krishna |
|
|
|
LamboG
Starting Member
4 Posts |
Posted - 2009-02-09 : 07:39:00
|
| I did originally have it in a function but was getting the error 'Only functions and extended stored procedures can be executed from within a function.' so I switched it to a sproc. It does have to do quite abit of work in the function/sproc.Jai, I've tried inserting directly but give me 'incorrect syntax'. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-09 : 08:12:39
|
What kind of resultset doEXEC dbo.usp_GetSupplyUtil '1A2345'return?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-02-09 : 09:17:02
|
| Post the script for dbo.usp_GetSupplyUtilMadhivananFailing to plan is Planning to fail |
|
|
|
LamboG
Starting Member
4 Posts |
Posted - 2009-02-09 : 09:33:11
|
| Thanks for your help guys.I've inserted into a temp table and seems to work ok. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 10:44:28
|
Yup...just checked it.. cheers  |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-09 : 11:12:24
|
| Hi Again LamboG:Does your procedure ever return more than 1 row? If not (and it doesn't actually change any data) -- then you should probably make it into a function.NB -- just noticed your earlier post re : Only functions and procedures can be executed from inside a function. Were you trying to use dynamic SQL? Maybe you could post your procedure -- see if there might be a more streamlined way of doing it? == of course, if you are happy with the way it works and it's not going to be a performance issue in the future than just live with it.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Can't store results from EXEC into variable