CODEWITHSUNDEEP
sqlt-sql
James Wilson
James Wilson

Reputation: 5150

SQL issue with a function

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

Upvotes: 1

Views: 56

Answers (3)

user565869
user565869

Reputation:

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.

Upvotes: 2

McGarnagle
McGarnagle

Reputation: 102723

Yes, just add it to your Where clause:

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

Upvotes: 1

PraveenVenu
PraveenVenu

Reputation: 8337

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

Upvotes: 0

Related Questions