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 |
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 2011-11-10 : 10:40:22
|
| Hi, I would like to store the return value of a stored procedure into a variable but can't find the syntax. The syntax below doesn't work.I can put it in a temporary table but would prefer to put it into a variable.EXEC @HourTarget = StoredProcedure @DateTime = @CurrentDateTime |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-10 : 10:47:58
|
| declare @HourTarget intEXEC @HourTarget = MyStoredProcedure @DateTime = @CurrentDateTimeselect @HourTarget or do you want an output parammeterEXEC @HourTarget = MyStoredProcedure @DateTime = @CurrentDateTime outThe parameter has to be defined as output in the SP too.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-10 : 10:48:03
|
| are you trying to return value through RETURN statement or through OUTPUT variable?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2011-11-10 : 10:56:46
|
Hi, yes i would prefer a return statement as in :exec @HourTarget = MyStoredProcedure @CurrentDateTimequote:
Originally posted by visakh16
are you trying to return value through RETURN statement or through OUTPUT variable?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-10 : 10:57:53
|
| Then see my first suggestion.in the SP you need to execute a return @val==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-10 : 11:00:53
|
quote:
Originally posted by insanepaul
Hi, yes i would prefer a return statement as in :exec @HourTarget = MyStoredProcedure @CurrentDateTimequote:
Originally posted by visakh16
are you trying to return value through RETURN statement or through OUTPUT variable?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
seehttp://www.sqlteam.com/article/stored-procedures-returning-data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-10 : 11:03:19
|
| Hope you know that by using RETURN you can return only integer values from stored procedure. generally its used in cases where we return status values. for all other cases, we return it using output parameters------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2011-11-10 : 11:23:21
|
I was tearing my hair out with the exec bit but you guys said it was correct which made me look into the SP itself.It was my fault, i needed to say 'return @hourtarget' in the stored procedure. Originally the SP was called from c# which didn't need the return statement.Anyway thanks for your helpquote:
Originally posted by nigelrivett
Then see my first suggestion.in the SP you need to execute a return @val==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
|
|
|
variable return stored procedure