May 1, 2009 at 3:03 pm
Hello,
I have this sproc. If the insert is successful, I want the sproc to return value 10. But it always returns 1 (the # of rows affected). And if I uncomment NOCOUNT, it always returns -1. Say I am ok with NOCOUNT being off. Is there a way to force it to return the value specified in the RETURN stmt and not the number of rows affected by insert/update/delete?
CREATE PROCEDURE [dbo].[InsertUser]
@id INT,
@firstname VARCHAR(50),
@lastname VARCHAR(50)
AS
BEGIN
--SET NOCOUNT ON
BEGIN TRY
INSERT INTO users (id, fname, lname) VALUES (@id, @firstname, @lastname)
RETURN 10
END TRY
BEGIN CATCH
RETURN 20
END CATCH
END
Thanks.
Nevermind. I can use return value parameter, not a big deal.
May 2, 2009 at 4:59 am
Hi
Maybe wrong execution to get the return value?
This works fine on my system:
CREATE PROCEDURE dbo.usp_TestReturn
AS
BEGIN TRY
RETURN 10
END TRY
BEGIN CATCH
RETURN 20
END CATCH
GO
DECLARE @ret INT
EXECUTE @ret = dbo.usp_TestReturn
PRINT @ret
GO
DROP PROCEDURE dbo.usp_TestReturn
GO
Greets
Flo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply