mercredi 6 juillet 2016

IF ELSE in Stored Procedure - Syntax Issue?

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.

If anyone can help, I would be most appreciative!

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

Thank you!

Aucun commentaire:

Enregistrer un commentaire