| Author |
Topic |
|
jparker
Posting Yak Master
118 Posts |
 Posted - 2005-09-15 : 11:34:48
|
| I have a stored procedure that does a calculation and collects the result via a select @cover_price as 'cover_price'What I want to do is pass this result to a variable for exampleset @cover_price = (exec spCoverPrice @size = @size, @quantity = @quantity)But I don't think I have the syntax right. Could you help please |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-15 : 12:35:49
|
| SQL Books Online has information about doing this in the "Returning Data from a Stored Procedure" topic. If you're using return codes, do it this way:EXECUTE @cover_price = spCoverPrice @size = @size, @quantity = @quantityYou can also use OUTPUT parameters.EXECUTE spCoverPrice @size = @size, @quantity = @quantity, @cover_price = @cover_price OUTPUT----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-15 : 12:50:06
|
Ryan, could you help me with a supplementary question on this please.I've seen chatter before that OUTPUT variable is preferred to RETURN value in case some sort of RAISERROR occurs, in that it might be the value of the RAISERROR that gets returned, rather than the intended value in the RETURN statement of the SProc that was called.We have our code peppered with RETURN values, and I have budgeted to replace them all with OUTPUTs instead, so I'd be really pleased to hear that I'm OK with RETURN variables  Thanks,Kristen |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-15 : 13:57:31
|
| I don't think I've heard anything about this, but I'll ask some of the folks around here if they know of any best practices. Stay tuned.Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-15 : 14:31:25
|
| Thanks Ryan |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-15 : 16:16:19
|
| Looks like you just need to ensure that your proc ALWAYS sets up a return code. From the developer who owns this stuff:The only time there is any strange behavior is if the proc doesn't explicitly return something. If every code path has an explicit RETURN [value], than the output of the proc is always going to be the [value] that is expected to be returned, regardless of any raiserrors.If the code is written so that in error cases, we do a raiserror followed by a RETURN without a value, then you get some interesting results that are different between SqlServer 2000 and SqlServer 2005. In 2000, the return will always be 0 in these cases, while in 2005 it is some odd negative number (-([severity of raiserror] - 10) if you want to know exactly).So the short answer is that as long as the TSQL always returns a specific value, there is no real value in switching to OUTPUT params. Only if there are cases of RETURN without a value are there any cases where the return might be unexpected, easily fixed by either OUTPUT params or specific error values added to the RETURN statements.----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-15 : 16:22:41
|
| That's good information! Thanks Ryan. And thanks Kristen for asking.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-15 : 16:27:07
|
| If our application doesn't care about the RETURN value, but RAISERROR is being used, what should we put for the RETURN value? Perhaps 1 to indicate success and 0 to indicate failure? Obviously if we don't care about the value, then our app isn't using it, but I'm wondering what we should do as a standard.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-16 : 01:06:12
|
| That's really good news Ryan, as it will save us a bundle of working making the switch over. Thanks for taking the time to find out for me.Tara - you've just got to be consistent, haven't you?Personally I would use 0=Success and anything else indicates failure - that way you can return a value indicating the specific error in case the "caller" can handle it in some wayKristen |
|
|
|
|
result to a variable from a stored procedure