SQL issue with a function

Question

CREATE FUNCTION [dbo].[MSG_FilterAutoship]
(
    @ItemID VARCHAR(50) = NULL
)
RETURNS 
@Autoship TABLE 
(
    DistID INT,
    BusCtrID INT
)
AS
BEGIN
    INSERT INTO @Autoship (DistID, BusCtrID)
        SELECT [as].Distid, 1 as busctrid
        FROM Autoship [as]
        INNER JOIN AutoshipDetail ad ON [as].DistID = ad.DistID
        INNER JOIN AS_DistributorCreditCard acc ON [as].DistID = acc.DistID
        WHERE [ad].InventoryID IN (@ItemID)
    RETURN 
END

What I need to have happen is if @ItemID is passed in a null value, then WHERE [ad].InventoryID IN (@ItemID) would basically not filter the results at all. Is this possible?

So basically if I pass in 120, 520 it would filters the results so only those two items are shown. If I pass in NULL then it would show all items.

Split function:

ALTER FUNCTION [dbo].[Split]
(
    @Delimiter CHAR,
    @Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(MAX))
AS

BEGIN
    DECLARE @NextPos INT,
        @LastPos INT

    SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
        @LastPos = 0

    WHILE @NextPos > 0
        BEGIN
            INSERT  @Result
                (
                    Data
                )
            SELECT  SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

            SELECT  @LastPos = @NextPos,
                @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
        END

    IF @NextPos <= @LastPos
        INSERT  @Result
            (
                Data
            )
        SELECT  SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

    RETURN
END

Here is the updated query that uses the split function:

AS
BEGIN
    INSERT INTO @Autoship (DistID, BusCtrID)
        SELECT [as].Distid, 1 as busctrid
        FROM Autoship [as]
        INNER JOIN AutoshipDetail ad ON [as].DistID = ad.DistID
        INNER JOIN AS_DistributorCreditCard acc ON [as].DistID = acc.DistID
        WHERE [ad].InventoryID IN (SELECT Data from dbo.Split(',', @ItemID)) or @ItemID IS NULL 
    RETURN 
END

score 2 · Accepted Answer · 2012-03-28 17:30:18Z

2

Sure. WHERE InventoryID = @ItemID OR @ItemID IS NULL. Note that TSQL will not automatically expand "1, 2, 3" into "IN (1, 2, 3)", rather it will compare InventoryID to "1, 2, 3" and presumably come up with no matches.

edited Mar 28, 2012 at 17:30

answered Mar 28, 2012 at 17:03

user565869

Sign up to request clarification or add additional context in comments.

7 Comments

James Wilson Over a year ago

this worked as far as I can tell, but I do not understand how it worked. I have a split function if they pass in 1,2,3,4,5 etc that allows it to work if they put in more than one itemid.

James Wilson Over a year ago

Well, it would read as this WHERE InventoryID IN (NULL) OR '@ItemID' IS NULL. How does it bypass the where in NULL, and '@ItemID' is NULL. Does the IN clause error out, and since the or statement is true it processes the sql? I'm thankful it works, just wish I understood why it worked better.

user565869 Over a year ago

The expression WHERE ID IN (NULL) OR @ItemID IS NULL returns true, because ID IN (NULL) is false but @ItemID IS NULL is true. The IN clause does not produce an error, it just says "nope, not a match".

user565869 Over a year ago

Note that while ID IN (NULL) simply returns false, depending on your implementation your split function could error out when fed a NULL value. I could give you a better answer with the actual query; your code above is leaving out the split, which is important.

James Wilson Over a year ago

Alright that helps me understand it, thank you for the solution @Jonofalltrades will post the function above in an edit.

|

McGarnagle · Accepted Answer · 2012-03-28 17:04:38Z

1

Yes, just add it to your Where clause:

WHERE @ItemID IS NULL OR [ad].InventoryID IN (@ItemID)

answered Mar 28, 2012 at 17:04

McGarnagle

103k31 gold badges235 silver badges264 bronze badges

Comments

PraveenVenu · Accepted Answer · 2012-03-28 17:03:15Z

0

This will return all rows if NULL is passed else return only the records that are in itemid

CREATE FUNCTION [dbo].[MSG_FilterAutoship]
(
    @ItemID VARCHAR(50) = NULL
)
RETURNS 
@Autoship TABLE 
(
    DistID INT,
    BusCtrID INT
)
AS
BEGIN
    INSERT INTO @Autoship (DistID, BusCtrID)
        SELECT [as].Distid, 1 as busctrid
        FROM Autoship [as]
        INNER JOIN AutoshipDetail ad ON [as].DistID = ad.DistID
        INNER JOIN AS_DistributorCreditCard acc ON [as].DistID = acc.DistID
        WHERE [ad].InventoryID IN (@ItemID) or [ad].InventoryID IS NULL
    RETURN 
END

answered Mar 28, 2012 at 17:03

PraveenVenu

8,3674 gold badges32 silver badges39 bronze badges

Collectives™ on Stack Overflow

SQL issue with a function

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related