IF ELSE in Stored Procedure - Syntax Issue?

Question

For some reason, I can't get the syntax right for this stored procedure. I have 2 variables. The issue is this - When we execute the stored procedure, the user may need to supply 1 integer for a parameter, multiple, or none. I want to allow all options to the user in this procedure, for both parameters. Below is what I have, at the moment. I keep trying different methods, and it seems like this should be simple to solve.

Right now, it doesn't seem to like the ELSE. I get an incorrect syntax near the word 'ELSE'. When I take those out, I get result windows for all 4 queries, even though only 1 of those IFs can possibly be true.

Can anyone help?

ALTER PROCEDURE sp_ProjectDocs_AuditDB_ByLB 
@BID VARCHAR = NULL, @LID VARCHAR = NULL
AS
BEGIN
    SET NOCOUNT ON

    --DECLARE @BID VARCHAR
    --, @LID VARCHAR 
    --SET @BID = '301,316,373,322,331'
    ----'301,316,373,322,331'
    --SET @LID = '1'
    ----'1,2,3,4'

    IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') = ''
        PRINT 'BOTH NULL'
        BEGIN
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
        END
    ELSE
        IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') <> ''
            PRINT 'LID NULL'
            BEGIN
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.LID IN (@LID)
            END
    ELSE        
        IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') = ''
            PRINT 'BID NULL'
            BEGIN
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.BID IN (@BID)
            END
    ELSE
    --IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') <> ''
        PRINT 'NEITHER NULL'
        BEGIN
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
            WHERE VWPD.LID IN (@LID) AND VWPD.BID IN (@BID)

        END
END
GO

--EXEC sp_ProjectDocs_AuditDB_ByLB '301','1'

GO

---

updated sql, as working through issues. This seems to run, but I get no results, when I can verify I do, by passing same parameters directly to the view in the select statements.

ALTER PROCEDURE sp_ProjectDocs_AuditDB_ByLB 
@BID VARCHAR = NULL, @LID VARCHAR = NULL
AS
BEGIN
    SET NOCOUNT ON

    --DECLARE @BID VARCHAR(100)
    --, @LID VARCHAR 
    --SET @BID = '301,316,373,322,331'
    ----'301,316,373,322,331'
    --SET @LID = '1'
    ----'1,2,3,4'

    IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') = ''
        BEGIN
            --PRINT 'BOTH NULL'
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
        END
    ELSE
        IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') <> ''
            BEGIN
                --PRINT 'BID NULL'
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.LID IN (@LID)
            END
    ELSE        
        IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') = ''
            BEGIN
                --PRINT 'LID NULL'
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.BID IN (@BID)
            END
    ELSE
    --IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') <> ''
        BEGIN
            --PRINT 'NEITHER NULL'
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
            WHERE VWPD.BID IN (@BID) AND VWPD.LID IN (@LID)

        END
END
GO

--EXEC sp_ProjectDocs_AuditDB_ByLB '301','1'

GO

Answer 1

Here's the final, working stored procedure.

ALTER PROCEDURE sp_ProjectDocs_AuditDB_ByLB 
@BID NVARCHAR(500) = NULL, @LID NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON

    --DECLARE @BID NVARCHAR(500)
    --, @LID NVARCHAR(500)
    --SET @BID = '301,316,373,322,331'
    ----'301,316,373,322,331'
    --SET @LID = '1'
    ----'1,2,3,4'


    IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') = ''
        BEGIN
            --PRINT 'BOTH NULL'
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
        END
    ELSE
        IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') <> ''
            BEGIN
                --PRINT 'BID NULL'
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.LID IN (SELECT * FROM SPLIT(@LID, ','))
            END
    ELSE        
        IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') = ''
            BEGIN
                --PRINT 'LID NULL'
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.BID IN (SELECT * FROM SPLIT(@BID, ','))
            END
    ELSE
    --IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') <> ''
        BEGIN
            --PRINT 'NEITHER NULL'
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
            WHERE VWPD.BID IN (SELECT * FROM SPLIT(@BID, ',')) AND VWPD.LID IN (SELECT * FROM SPLIT(@LID, ','))

        END
END
GO

--EXEC sp_ProjectDocs_AuditDB_ByLB '301','1'

GO

Answer 2

Try this, You have to give the Print statement inside the Begin-End Block.

ALTER PROCEDURE sp_ProjectDocs_AuditDB_ByLB 
@BID VARCHAR = NULL, @LID VARCHAR = NULL
AS
BEGIN
    declare @sqlquery varchar(max)

    SET NOCOUNT ON

    --DECLARE @BID VARCHAR
    --, @LID VARCHAR 
    --SET @BID = '301,316,373,322,331'
    ----'301,316,373,322,331'
    --SET @LID = '1'
    ----'1,2,3,4'

    IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') = ''

        BEGIN
        set @sqlquery = '
        PRINT ''BOTH NULL''
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD'
        END
    ELSE
        IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') <> ''

            BEGIN
            set @sqlquery = '
            PRINT ''LID NULL''
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.LID IN(' + @LID+')'
            END
    ELSE        
        IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') = ''

            BEGIN
            set @sqlquery = '
            PRINT ''BID NULL''
                SELECT VWPD.*
                FROM vw_ProjectDocs_AuditDB VWPD
                WHERE VWPD.BID IN ('+ @BID+')'
            END
    ELSE
    --IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') <> ''

        BEGIN
        set @sqlquery = '
        PRINT ''NEITHER NULL''
            SELECT VWPD.*
            FROM vw_ProjectDocs_AuditDB VWPD
            WHERE VWPD.LID IN ('+@LID+') AND VWPD.BID IN ('+@BID+')'

        END
    EXEC (@sqlquery)
END
GO

Answer 3

You have to put PRINT inside BEGIN/END block.

Please note that messages 'LID NULL' and 'BID NULL' aren't correct based on your IF.