none
OutParamter returning null values RRS feed

  • Question

  • Hi Team,

    When error coming in stored procedure ,output parameters returning null values.


    Is there common place in stored procedure to  set that out parameter as 0 and get it back to asp.net procedure call.

    Regards,

    Prasad.

    Friday, June 14, 2019 12:32 AM

All replies

  • You should use error handling in Stored Procedure to return custom values to out parameter. Error handling in TSQL is well explained with example on below URL, please refer. 

    I think you can use try catch and GOTO, and set out parameter as zero in GOTO step.

    https://www.c-sharpcorner.com/UploadFile/f0b2ed/exception-handling-in-sql-server/

    ___________________________________________________________________
    Please remember to mark the replies as answers if they help.

    Friday, June 14, 2019 12:59 AM

  • Is there common place in stored procedure to  set that out parameter as 0 and get it back to asp.net procedure call.


    Hi Prasad,

    As Hari replies you should have some error handling that catches the errors, and in there you can set the parameters to null. The other thing you can do is to set then explicitly to 0 in the procedure signature where they are declared:

    CREATE PROCEDURE dbo.pr_MyProc @inputParam1 int,
                                   @outputParam1 int = 0 OUT
    ...

    In the code above the @outputParam1 parameter is an output param, but it is assigned a default value of 0.

    Hope this helps!

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Friday, June 14, 2019 2:25 AM
    Moderator
  • The other thing you can do is to set then explicitly to 0 in the procedure signature where they are declared:

    CREATE PROCEDURE dbo.pr_MyProc @inputParam1 int,
                                                                 @outputParam1 int = 0 OUT
    ...

    In the code above the @outputParam1 parameter is an output param, but it is assigned a default value of 0.

    Nah, that is a default value if you do not pass the variable. If the variable is passed explicitly, the 0 is ignored.

    The context for Prasad's question is unclear, but say that you call a stored procedure with an output parameter. The SP assigns the output parameter a value, and later there is an error in the procedure which is caught by a CATCH handler in an outer scope. In this case, the value inside in the procedure will not be available in the caller. This is because the semantics in SQL Server is copy-in-copy-out. That is, the variable is not passed by reference, so if the procedure is aborted prematurely, the output value is not set.
    This script demonstrates:

       CREATE PROCEDURE silly_test @x int = 0 OUTPUT AS
          IF @x <> 0 SELECT @x = 1123
          SELECT 1 / 0
       go
       SET XACT_ABORT OFF
       DECLARE @x int = 99
       EXEC silly_test @x OUTPUT
       PRINT '@x is ' + convert(varchar, @x)
       go
       DECLARE @x int = 99
       BEGIN TRY
          EXEC silly_test @x OUTPUT
       END TRY
       BEGIN CATCH
          PRINT error_message()
       END CATCH
       PRINT '@x is ' + convert(varchar, @x)
       go
       DROP PROCEDURE silly_test

    The output is:

       -----------
       Msg 8134, Level 16, State 1, Procedure silly_test, Line 3 [Batch Start Line 4]
       Divide by zero error encountered.

       @x is 1123

       -----------

       (0 rows affected)

       Divide by zero error encountered.
       @x is 99

    In the first execution, the procedure completes despite the error, and @x is set to 1123, as @x is not 0.

    In the second execution, @x is not returned as the SP is intercepted by the CATCH block.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, June 14, 2019 11:32 AM
  • Hi Erland,

    So I was not saying that the output param with a default value of 0 would always come back as 0 if there was an error. Obviously if a value is assigned, either in the call or later during the proc execution, the value of the param would be whatever it is assigned.

    Also, as in my suggestion the param is default, if you doesn't pass it at all well then you wont get a value back. Based on the context of the OP's question where his param comes back as NULL, e.g. no value has been passed in - then he'd get 0 back if an exception happened.


    http://www.nielsberglund.com | @nielsberglund

    Friday, June 14, 2019 3:22 PM
    Moderator
  • Hi,

    Thank you all for your reply

    My scenario would be

    I have to get Table id value which is newly inserted

    create procedure sp_test

    @x int output=0

    as 

    begin

    declare @maxid int

    set @maxid =select max(id)+1 from test_table 

    insert into testt_table (id,......) values(@maxid,....)

    select @x=@maxid

    if @@error<> 0

    goto Errorstatement

    Errorstatement:

    set @x=0

    return 1

    end

    If any exception raised in above insert ,i am getting null value but not 0.Eventhough we set in some section like  Errorstatement .Its not returning 0 value ,but getting x as null  to asp.net code.

    Friday, June 14, 2019 8:52 PM
  • Also, as in my suggestion the param is default, if you doesn't pass it at all well then you wont get a value back. Based on the context of the OP's question where his param comes back as NULL, e.g. no value has been passed in - then he'd get 0 back if an exception happened.

    The default value only applies if the parameter is not passed at all. But in that case, there is nothing to pass back a value to.

    If the parameter is passed as NULL, and the procedure is intercepted by a CATCH block in the caller, the value will remain NULL, as copy-out never happens.

    If there is an exception which is not caught in the caller, whatever value that was assigned in the procedure will be returned, then it depends on the nature of the error. If the error is such that execution continues on the next statement, the value will be returned. If the error aborts execution, there is noting to return value to. If the error aborts the current scope - this happens for instance on a missing table - the copy-out to the parameter does not happen.

    Error handling in SQL Server is indeed a confusing matter.

    CREATE OR ALTER PROCEDURE silly_test @x int = 0 OUTPUT AS
       IF @x <> 0 OR @x IS NULL SELECT @x = 1123
       SELECT 1 / 0
    go
    SET XACT_ABORT OFF
    DECLARE @x int
    EXEC silly_test @x OUTPUT
    PRINT '@x is ' + isnull(convert(varchar, @x), 'NULL')
    go
    DECLARE @x int
    BEGIN TRY
       EXEC silly_test @x OUTPUT
    END TRY
    BEGIN CATCH
       PRINT error_message()
    END CATCH
    PRINT '@x is ' + isnull(convert(varchar, @x), 'NULL')
    go
    CREATE OR ALTER PROCEDURE silly_test @x int = 0 OUTPUT AS
       IF @x <> 0 OR @x IS NULL SELECT @x = 1123
       SELECT * FROM nosuchtable
    go
    DECLARE @x int
    EXEC silly_test @x OUTPUT
    PRINT '@x is ' + isnull(convert(varchar, @x), 'NULL')
    go
    DROP PROCEDURE silly_test


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, June 14, 2019 8:58 PM
  • To explain exactly why things does not work as you expect, I would need to see a full repro that demonstrates the problem. Error handling in SQL Server is a very messy topic, because different errors have different effects.

    But the code you have can never work. To wit:

      select @x=@maxid
      if @@error<> 0
      goto Errorstatement

    @@error is set after every statement, so here you are testing whether the statement "select @x=@maxid" failed.

    But you should not rely on @@error for error-checking. That's how we had to do it way back when SQL 2000 ruled the world, but that's long ago. YOu should use TRY-CATCH, and this article on my web site tells how you should structure your error handling: http://www.sommarskog.se/error_handling/Part1.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 15, 2019 8:05 AM