January 9, 2008 at 10:47 am
When asking SSMS to execute a stored procedure, the code generated is:
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[BiWeeklyUpdate]
@YYPP = N'0801'
USE [HR_Joined]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[BiWeeklyUpdate]
@YYPP = N'0801'
SELECT'Return Value' = @return_value
GO
I have been able to find the BOL documentation of EXECUTE where @return_value = procedureName is supported, and in the Select documentation where column_alias = expression is allowable. The latter seems somewhat foreign to me (a beginner); any reason to use that form instead of:
expresion AS column_alias?
January 9, 2008 at 11:20 am
You've got two somewhat different things going on here:
- The first one is trying to capture the return code of a Stored Procedure into a variable for later use.
- The second one is assigning an alias to column in a query or DML statement.
As far the second part - there are 2 notations running around. The "classic" or older notation is the one with the equal where the value is "assigned" to the alias name; the newer, ANSI-compliant notation is the "value AS alias" notation.
For what it's worth - the classical notation is marked as deprecated (meaning - it's being phased out and will stop being supported in a future version). Note that I'm only talking about column aliases: as far as I know, retrieving the return value from a SP still continues to work the same.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2008 at 11:36 am
EXEC @ReturnValue = procname @param='value'
We use this in a pretty common fashion with the added code:
IF @ReturnValue <> 0
BEGIN
--handle the error
It works under the assumption that the procedure is going to return a value that is zero for a successful execution, or some other value for an unsuccesful one.
It's available right here in the EXEC BOL entry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2008 at 11:56 am
Thank you both!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply