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

Aside from the logical errors you should consider renaming your procedure. The sp_ prefix can cause some performance problems. Either change the prefix, or my preference is to just drop the prefix entirely. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange, Jul 06 '16 at 13:46
I noticed a couple of other potential problems here. You have passed in varchars but did not specify the size. That means they will default to 30. It is generally best to be explicit. The bigger issue though is you are using those varchar values with an IN predicate. If you are passing multiple values that won't work. Also, you have a potential performance issue with this procedure because the execution plan can easily get confused when the resulting query changes each execution. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/ fwiw, I was not the one who downvoted you — Sean Lange, Jul 06 '16 at 13:53
Thank you, Sean! That default varchar was part of my problem. Now I just need to detect if I am passing more than 1 number for a parameter, so I can pass it as a multi-list to the IN where clause. hmm.... I have an idea! — missscripty, Jul 06 '16 at 14:12
If you need multiple values you have a few options (some are better than others). I listed these in order of preference. 1- pass in a table valued parameter instead, 2- create a splitter and parse the values, 3- use dynamic sql. — Sean Lange, Jul 06 '16 at 14:15
I think I want #2. The user will have the list of IDs to pass. It will be easiest for them to pass the comma delimited list as the parameter, and then have the proc split it out to the in statement. my wheels are turning lol — missscripty, Jul 06 '16 at 14:18

score 3 · Answer 1 · answered Jul 06 '16 at 13:31

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.

answered Jul 06 '16 at 13:31

tezzo

10,858
1
25
48

DineshDB · Answer 2 · 2016-07-06T14:30:57.887

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

edited Jul 06 '16 at 14:30

answered Jul 06 '16 at 13:27

DineshDB

5,998
7
33
49

this gets me passed the else errors, but now I get no results, with the message telling me the correct 'neither null'. I can query the view directly, with the parameters I tested (bid = 301, lid = 1) and I get 10 results (which I should). but executing from the stored proc, I get 0 results. Any thoughts? – missscripty Jul 06 '16 at 13:35
Can you replace PRINT with SELECT and try again – DineshDB Jul 06 '16 at 13:41
Instead of PRINT 'BID NULL', You just try with SELECT 'BID NULL' – DineshDB Jul 06 '16 at 13:44
but what I need are the results from the sql statement with the parameters passed through. I can try that, but it's not what I'm trying to do. – missscripty Jul 06 '16 at 13:47
ok new thing to tell you. I passed parameters '1','1' and I get the right results, but when I try '301','1' I get 0, There are 10 records from the view, with bid 301 and lid 1. Can't figure out why its not getting the correct results. I'm not even sure this will work, when I need to pass a comma delimited list for 1 of the parameters (generally BID). – missscripty Jul 06 '16 at 13:54
I just try the same in my Local DB, It works fine and give results. – DineshDB Jul 06 '16 at 13:54
VWPD.BID and VWPD.BID are INT or VARCHAR values? – DineshDB Jul 06 '16 at 13:59
Okay, Just check your VWPD.BID and VWPD.BID datatypes are INT or VARCHAR? – DineshDB Jul 06 '16 at 14:11
in the table, bid and lid are int. I set the parameter as varchar, so I could pass a delimited list, if needed, which is also why I made the where be IN () instead of = – missscripty Jul 06 '16 at 14:19
Just try the Dynamic SQL, It simplifies the delimited method – DineshDB Jul 06 '16 at 14:21
Thanks, I wanted to avoid dynamic sql because of injection risks. I edited my original question with the final working code. I just use a split function to split the list on a comma, with a default null on the parameter. Whether I provide NULL, 1 value or multiple, it works!!!! – missscripty Jul 06 '16 at 14:58
Thank you for all your help! – missscripty Jul 06 '16 at 14:58

score 1 · Answer 3 · edited Jul 07 '16 at 02:34

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

IF ELSE in Stored Procedure - Syntax Issue?

3 Answers3