1

Using SQL Server, I'm executing a stored procedure that may sometimes throw a Raiserror and sometimes not. Now I tried to use the returned parameter for tests, but it is not passed

errCount=-1
query="exec [db\].[testErrGoLang]  @dev=10, @dev2=0       "
rows, err = db.QueryContext(ctx, query, sql.Named("err", sql.Out{Dest: &errCount}))

After this code errCount has value -1, but must 0

ALTER PROCEDURE [dbo].[testErrGoLang]
@dev int,
@dev2 int,
@err int OUTPUT
AS
BEGIN
declare @ErrMes nvarchar(250)
set @err=0
SET NOCOUNT ON;

    begin try
     select @dev/@dev2
    end try 
        begin catch 
        BEGIN
                set @err=1
                select @ErrMes = '!Text of Raiserror!'
                RAISERROR(@ErrMes,16,1)
        END
        end catch

END

I'm using "github.com/denisenkom/go-mssqldb" and at their documentation example:

sqltextcreate := `
CREATE PROCEDURE spwithoutputandrows
    @bitparam BIT OUTPUT
AS BEGIN
    SET @bitparam = 1
    SELECT 'Row 1'
END
`
rows, err := db.QueryContext(ctx, "spwithoutputandrows", sql.Named("bitparam", sql.Out{Dest: &bitout}))
Dale K
  • 25,246
  • 15
  • 42
  • 71
Spison
  • 21
  • 2
  • Your query fails to declare the output parameter altogether. It should be `"exec [db\].[testErrGoLang] @dev=10, @dev2=0,@err=@err output`. Note that the first `@err` is the parameter name and the second `@err` is your query parameter, these are not automatically or directly related. I'm not entirely sure how the second example purports to work and how `QueryContext` distinguishes between an RPC call (which has no parameters) and an `EXEC` statement (which does), but a hybrid approach where parameters are sometimes declared explicitly and sometimes not can't work in any case. – Jeroen Mostert Apr 05 '23 at 09:36

0 Answers0