Returning 2 output parameters in Stored Procedure
-
Hi guys, I'm trying to get 2 values from a stored procedure: one telling me the execution was successful and another for telling me what the newly inserted ROW ID is. I currently have the following:
USE [xyz] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spName] ( @vchName VarChar (50), @intSuccess Numeric(9) OUT ) AS SET XACT_ABORT ON BEGIN TRANSACTION INSERT INTO Table ( vchName ) VALUES ( @vchName ) IF @@ERROR <> 0 BEGIN SELECT @intSuccess = 0 ROLLBACK END ELSE SELECT @intSuccess = 1 /* SET NOCOUNT ON */ COMMIT TRANSACTION SET XACT_ABORT OFF RETURN @intSuccessCurrently it returns the value "@intSuccess" ONLY. I want to add the line "SELECT @@IDENTITY AS inLatestID" right under the insert statement so I can get the latest ID value of the record that was just newly inserted. I think I will need to return another value alongside @intSuccess, but I'm not sure how that works in the Stored Procedure -
Hi guys, I'm trying to get 2 values from a stored procedure: one telling me the execution was successful and another for telling me what the newly inserted ROW ID is. I currently have the following:
USE [xyz] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spName] ( @vchName VarChar (50), @intSuccess Numeric(9) OUT ) AS SET XACT_ABORT ON BEGIN TRANSACTION INSERT INTO Table ( vchName ) VALUES ( @vchName ) IF @@ERROR <> 0 BEGIN SELECT @intSuccess = 0 ROLLBACK END ELSE SELECT @intSuccess = 1 /* SET NOCOUNT ON */ COMMIT TRANSACTION SET XACT_ABORT OFF RETURN @intSuccessCurrently it returns the value "@intSuccess" ONLY. I want to add the line "SELECT @@IDENTITY AS inLatestID" right under the insert statement so I can get the latest ID value of the record that was just newly inserted. I think I will need to return another value alongside @intSuccess, but I'm not sure how that works in the Stored ProcedureYou can have multiple out parameters, but you do not need to return success/failure as an out parameter. You can use standard try/catch (sql 2005/8) or raiseerror for earlier versions to check for errors. Check out BOL for more details
Bob Ashfield Consultants Ltd
-
You can have multiple out parameters, but you do not need to return success/failure as an out parameter. You can use standard try/catch (sql 2005/8) or raiseerror for earlier versions to check for errors. Check out BOL for more details
Bob Ashfield Consultants Ltd
-
I suppose that makes sense. Normally I check if the return value is > 0, I suppose if a Stored Procedure executes successfully, then my parameter should be > 0 and I can just check that. Thanks for your reply.