ExecuteNonQuery and RETURN

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  May 1, 2009 at 3:03 pm

  #131061

  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.

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 2, 2009 at 4:59 am

  #987693

  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