NUll values for input parameters

  • Hello all,

    I have a stored procedure that begins:

    Create Procedure AllocationAssistant_SetAllocations

    (

    @RoadShowID int,

    @RoadShowEventID int = NULL,

    @SellerID int = NULL,

    @EmployerID int = NULL,

    @JobsAllocated int = NULL,

    @SellerProjection int = NULL,

    @SellerEnrolled int = NULL,

    @ChangedByUserID int = NULL,

    @ChangedBySellerID int = NULL,

    @ChangeComments nvarchar(255) = NULL

    )

    AS

    SET NOCOUNT ON

    SET ANSI_NULLS OFF

    --First check if this row already exists

    IF NOT EXISTS

    (

    SELECT 1 FROM dbo.tblRoadShowAllocation

    WHERE

    RoadShowID = @RoadShowID AND

    RoadShowEventID = @RoadShowEventID AND

    SellerID = @SellerID AND

    EmployerID = @EmployerID

    )

    --This is a new record insert

    BEGIN ...

    The problem is that @RoadShowEventID and @SellerID can be null. If ANSI_NULLS are OFF the test works when either @RoadShowEventID or @SellerID are NULL.

    But in the context of the stored procedure I cant't seem to SET ANSI_NULLS OFF. SQL Serve insists on turning them on at the top of the procedure and I can't put GO

    after my SET ANSI_NULLS OFF because it is in the procedure!

    Is there a way to rewrite the SELECT so that ANSI_NULLS don't matter. Maybe using ISNULL?

    Thanks!

    Jonathan

  • You're supposed to set that before the stored proc. The procedure will emulate whatever the ANSI_NULLS setting was at the time when it was created.

    So, -

    SET ANSI_NULLS ON

    GO

    create procedure

    blah

    ...

    go

    SET ANSI_NULLS OFF

    go

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks! I was confused about that. Works now.

    One thing though; if a user ALTERs the procedure with Managment Studio the server starts the script with SET ANSI_NULLS ON. (I think this might be cotrolled with a setting when the SQL Server was installed). If the script is then executed would the procedure then assume ANSI_NULLS are on or would the original CREATE script still win?

  • Greetings Jonathan,

    The crux of the problem is comparing NULL using logical operators. NULL does not compare!

    I think something on the order of the following modification will remedy your issue:

    If you want the SELECT to 'fail' when either of them is NULL then:

    WHERE ...

    (

    (RoadShowEventID = @RoadShowEventID AND @RoadShowEventID IS NOT NULL)

    AND -- Change this to OR to succeed if either/both are NULL

    (SellerID = @SellerID AND @SellerID IS NOT NULL)

    ) ...

    As a suggestion, I would change the IF NOT EXISTS(SELECT 1...) to IF EXISTS(SELECT *) and reverse the new/existing row logic.

    HTH,

    Art

  • I agree about NULL not comparing and am not thrilled with I am doing here. The data in this table is really odd, however. In some cases RoadShowEventID and/or SellerID can be NULL in the table and still play a role in uniquely identifying the row. (Codd would be horrified). So I need to compare the values for the input parameters to the values in the table even if they are NULL.

  • Hi Jonathan

    Yes, you could use ISNULL to swap null values for "unrealistic" integer values for your ID's, so that the test would fail. However, if any of your ID parameters evaluate to null, does it make sense to go so far as testing for existence of the row?

    IF @RoadShowID IS NULL

    OR @RoadShowEventID IS NULL

    OR @SellerID IS NULL

    OR @EmployerID IS NULL

    -- need all 4 integer params, so fail here

    RETURN 0

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Use the OR version...

    You can leave ANSI NULLs ON and it will still work.

    Test it to be sure, dink with the AND/OR but the "column = @var AND @var IS NOT NULL" code will allow the use of indexes in the query plan and solve the one or more NULL parameters issue.

    For a semi-exhaustive treatise...

    http://www.worldwidewords.org/qa/qa-pro1.htm

  • Sanity check here ...

    The relevant code in my proc looks like this

    --First check if this row already exists

    IF EXISTS

    (

    SELECT 1 FROM dbo.tblRoadShowAllocation

    WHERE

    RoadShowID = @RoadShowID AND

    RoadShowEventID = @EventID AND

    SellerID = @SellerID AND

    EmployerID = @EmployerID

    )

    BEGIN

    --This is an update of an existing record

    (I took the advice and changed to IF EXISTS and swithced IF ELSE code).

    RoadShowEventID and SellerID can be NULL in the table and still play a role in uniquely identifing the row. So when @RoadShowEventID and @SellerID are NULL I need to compare to the values in the table.

    How can the OR ISNull accomplish this?

    Thanks for you help!

  • Are you saying the column must match the parameter -OR- both the column and the parameter are null?

    Wouldn't that be...

    WHERE (RoadShowEventID = @RoadShowEventID OR (RoadShowEventID IS NULL AND @RoadShowEventID IS NULL)

    AND ...

  • I mean the data can look like this

    RoadShowIDRoadShowEventIDSellerIDEmployerID

    35NULLNULL567

    35345NULL567

    35NULL456567

    35345456567

    I need to find a row based on the input parameters for these four columns.

    Thanks again.

  • If I'm understanding this correctly, I believe that would be...

    WHERE RoadShowID = @RoadShowID

    AND (RoadShowEventID = @RoadShowEventID OR (RoadShowEventID IS NULL AND @RoadShowEventID IS NULL))

    AND (SellerID = @SellerID OR (SellerID IS NULL AND @SellerID IS NULL))

    AND EmployerID = @EmployerID

    if RoadShowID and EmployerID can be null too, then

    WHERE (RoadShowID = @RoadShowID OR (RoadShowID IS NULL AND @RoadShowID IS NULL))

    AND (RoadShowEventID = @RoadShowEventID OR (RoadShowEventID IS NULL AND @RoadShowEventID IS NULL))

    AND (SellerID = @SellerID OR (SellerID IS NULL AND @SellerID IS NULL))

    AND (EmployerID = @EmployerID OR (EmployerID IS NULL AND @EmployerID IS NULL))

  • Thanks! That does what I need to do without worrying about ANSI_NULLS.

    Thanks for the help everyone.

  • From SQL Server Books Online:

    "For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed."

    This means that it uses the ANSI_NULLS setting that was in effect at the time the procedure was created, and the SET ANSI_NULLS statement does nothing inside a stored procedure. If you want the stored procedure to have ANSI_NULLS on, do this in your code:

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE ...rest of procedure code...

  • SELECT 1 FROM dbo.tblRoadShowAllocation

    WHERE

    RoadShowID = @RoadShowID AND

    RoadShowEventID = @RoadShowEventID AND

    SellerID = @SellerID AND

    EmployerID = @EmployerID

    )

    The problem is that @RoadShowEventID and @SellerID can be null. If ANSI_NULLS are OFF the test works when either @RoadShowEventID or @SellerID are NULL.

    I guess the intention is that if the inputs are null then all values for that input will be allowed - I find the neatest (though possibly not optimal) SQL for this sort of thing is to do it like:

    SELECT 1 FROM dbo.tblRoadShowAllocation

    WHERE

    RoadShowID = IsNull(@RoadShowID,) AND

    RoadShowEventID = IsNull(@RoadShowEventID,RoadShowEventID) AND

    SellerID = IsNull(@SellerID,SellerID ) AND

    EmployerID = IsNull(@EmployerID,EmployerID)

    Of course this assume that in the ID values in the DB itself there are no Nulls - only that the sproc parm may have Nulls and if parm is Null we want it to consider all rows for that particular parm

  • You could try this:

    SELECT 1 FROM dbo.tblRoadShowAllocation

    WHERE

    Coalesce(RoadShowID,-1) = Coalesce(@RoadShowID,-1) AND

    Coalesce(RoadShowEventID,-1) = Coalesce(@EventID,-1) AND

    Coalesce(SellerID,-1) = Coalesce(@SellerID,-1) AND

    Coalesce(EmployerID,-1) = Coalesce(@EmployerID,-1)

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply