Rich "The Hubbins" Claussen - Assigning the Scalar from a Stored Procedure

Thursday, August 24, 2006

Assigning the Scalar from a Stored Procedure

This is just one of those little reminders to self. Self, you never seem to remember how to assign the return value from a stored procedure to a variable in SQL. I try various forms and always seem to miss the right one. So, in an effort to mark it for my future use, I place it here in the blog.

One does NOT accomplish it through any of these methods:

DECLARE @ReturnedValue INT
    SET @ReturnedValue = StoredProcedure @Param1, @Param2
IF( @ReturnedValue = @Something )
BEGIN
    --Do This Stuff
END
 
DECLARE @ReturnedValue INT
    SET @ReturnedValue = StoredProcedure ( @Param1, @Param2 )
IF( @ReturnedValue = @Something )
BEGIN
    --Do This Stuff
END
 
DECLARE @ReturnedValue INT
    SET @ReturnedValue = EXEC StoredProcedure @Param1, @Param2
IF( @ReturnedValue = @Something )
BEGIN
    --Do This Stuff
END
 
DECLARE @ReturnedValue INT
    SET @ReturnedValue = EXEC StoredProcedure ( @Param1, @Param2 )
IF( @ReturnedValue = @Something )
BEGIN
    --Do This Stuff
END

One does by this method:

DECLARE @ReturnedValue INT
EXEC    @ReturnedValue = StoredProcedure @Param1, @Param2
IF( @ReturnedValue = @Something )
BEGIN
    --Do This Stuff
END

I just have to remember the order. I suppose it makes sense when I see it, but I forget when it's time to type it. It's just too easy to get mixed up with Function format.

Developer | SQL | Tips

Thursday, August 24, 2006 5:50:34 PM (Pacific Daylight Time, UTC-07:00)

Comments [0]

Comments are closed.