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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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