locked
return null RRS feed

  • Question

  • hi
    below is my existing procedure .my output parameter has tinyint data type.currently if my column value is null in that case i m returning 0.but now i want to return null in case if my column has null value. so can u guys tell me what changes require to complete my task.
    should i change data type of my output parameter.

    CREATE PROCEDURE [dbo].[pr_GetAutoConfirmOrdersSetting]
     @vnSenderID int,
     @vnRecipientID varchar(255),
     @rtAutoConfirmOrders tinyint output
    AS
    SET NOCOUNT ON


    SELECT @rtAutoConfirmOrders = ValueBit
        FROM TradingPartner_ExtraInfo
        WHERE MemberID = @vnRecipientID
          AND PartnerID = @vnSenderID
       AND ExtraInfoID = 8

    IF @rtAutoConfirmOrders IS NULL
        SET @rtAutoConfirmOrders = 0 
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON

    Wednesday, February 25, 2009 8:16 PM

Answers

  • Like I said earlier: Just remove the IF statement; what are you seeing that makes you believe this will not work?  Here is what I am seeing:

     

    alter PROCEDURE [dbo].[pr_GetAutoConfirmOrdersSetting]
     @vnSenderID int,
     @vnRecipientID varchar(255),
     @rtAutoConfirmOrders tinyint output
    AS
    SET NOCOUNT ON


    SELECT @rtAutoConfirmOrders = ValueBit
        FROM TradingPartner_ExtraInfo
        WHERE MemberID = @vnRecipientID
          AND PartnerID = @vnSenderID
       AND ExtraInfoID = 8

    --IF @rtAutoConfirmOrders IS NULL
    --    SET @rtAutoConfirmOrders = 0
    GO

    declare @outputStuff tinyint

    exec dbo.pr_GetAutoConfirmOrdersSetting 1, 2, @rtAutoConfirmOrders=@outputStuff output

    select @outputStuff [@outputStuff]

    /* -------- Output Before Commenting Out IF statement:
    @outputStuff
    ------------
    0
    */


    /* -------- Output After Commenting Out IF statement:
    @outputStuff
    ------------
    NULL
    */

    • Marked as answer by nofriends Thursday, February 26, 2009 9:06 AM
    Wednesday, February 25, 2009 11:03 PM

All replies

  • Just take out the IF statement?  Maybe I am not understanding the issue.
    Wednesday, February 25, 2009 8:21 PM
  • Thanks for the reply.as per my knowledge u can not return null value for int/tinyint output parameter.if i remove the if statement i will get error of returning null value for int o/p parameter.

    hope it make some sense.
    Wednesday, February 25, 2009 8:37 PM
  • I don't know what you are talking about:

    alter procedure dbo.what @x tinyint output
    as
    return
    0
    go

    declare @a tinyint

    exec what @x=@a output

    select @a [@a]

    /* -------- Sample Output: --------
    @a
    ----
    NULL
    */



    ???

    Wednesday, February 25, 2009 8:59 PM
  • okay let me explain again.
    i have a procedure which has output parameter as int data type.

    can we return null value for this output parameter.

    if yes then how.if not then what is other way.

    Thanks for your effort.
    Wednesday, February 25, 2009 9:07 PM
  • I still don't get it, but I am out of time and I will have to look at this later.  Hopefully someone can give you a good short answer.
    Wednesday, February 25, 2009 9:21 PM
  • You can delcare the output parameter as
    @outputparm  int=null output
    • Proposed as answer by Roger Binny Wednesday, February 25, 2009 10:34 PM
    Wednesday, February 25, 2009 10:02 PM
  • Like I said earlier: Just remove the IF statement; what are you seeing that makes you believe this will not work?  Here is what I am seeing:

     

    alter PROCEDURE [dbo].[pr_GetAutoConfirmOrdersSetting]
     @vnSenderID int,
     @vnRecipientID varchar(255),
     @rtAutoConfirmOrders tinyint output
    AS
    SET NOCOUNT ON


    SELECT @rtAutoConfirmOrders = ValueBit
        FROM TradingPartner_ExtraInfo
        WHERE MemberID = @vnRecipientID
          AND PartnerID = @vnSenderID
       AND ExtraInfoID = 8

    --IF @rtAutoConfirmOrders IS NULL
    --    SET @rtAutoConfirmOrders = 0
    GO

    declare @outputStuff tinyint

    exec dbo.pr_GetAutoConfirmOrdersSetting 1, 2, @rtAutoConfirmOrders=@outputStuff output

    select @outputStuff [@outputStuff]

    /* -------- Output Before Commenting Out IF statement:
    @outputStuff
    ------------
    0
    */


    /* -------- Output After Commenting Out IF statement:
    @outputStuff
    ------------
    NULL
    */

    • Marked as answer by nofriends Thursday, February 26, 2009 9:06 AM
    Wednesday, February 25, 2009 11:03 PM
  • really f*** up of me.Thank u so much.its working.
    Thursday, February 26, 2009 9:07 AM