Nested If in stored proc causing syntax error

Question

I am creating a stored proc, it queries the table "Entries". The proc has the parameters @ID, which is an int, and @APPROVED, which is a bit. I'm using SQL Server 2005

If approved is false I want to do something different than if it is true. I have the following written. When I try to create it I get "Incorrect syntax near the keyword 'END'.".

If I remove the nested if the error goes away however from what I've read this is perfectly valid syntax. Could anyone tell me where I am going wrong?

CREATE Procedure [dbo].[GetEntry](@ID int,@APPROVED bit)
AS
IF @APPROVED = 0
BEGIN 
    --see if the unapproved entry has already been viewed 
    IF (SELECT COUNT(*) 
    FROM [dbo].[Entries]
    WHERE EntryId = @ID AND Approved = @APPROVED AND Viewed = 0) > 0
    BEGIN

    END

END

Any help would be really appreciated. Thanks!

you have an empty BEGIN END, something needs to be in there — Taryn
– Taryn, Commented Sep 19, 2012 at 17:41
Ugh now I feel stupid. I was just building the structure for the proc, testing the syntax after every few lines I would add. Thanks. If you wanna make an answer I will make it the accepted answer. — TheMethod
– TheMethod, Commented Sep 19, 2012 at 17:44

Taryn · Accepted Answer · 2012-09-19 17:43:46Z

1

You have to do something in the BEGIN/END, it cannot be empty:

CREATE Procedure [dbo].[GetEntry](@ID int,@APPROVED bit)
AS
IF @APPROVED = 0
BEGIN 
    --see if the unapproved entry has already been viewed 
    IF (SELECT COUNT(*) 
            FROM [dbo].[Entries]
            WHERE EntryId = @ID AND Approved = @APPROVED AND Viewed = 0) > 0
    BEGIN
        -- do something here
        select *
        from [dbo].[Entries]
    END

END

answered Sep 19, 2012 at 17:43

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Comments

Aaron Bertrand · Accepted Answer · 2012-09-19 17:43:29Z

1

You need to do something inside BEGIN/END, and a much more efficient check is EXISTS vs. manually obtaining a count (which has to scan the entire clustered index). You don't really care if the result is 2 or 5 or 27,654, right?

IF EXISTS (SELECT 1 FROM dbo.Entries WHERE ...)
BEGIN
    PRINT 'It exists!';
END

answered Sep 19, 2012 at 17:43

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Collectives™ on Stack Overflow

Nested If in stored proc causing syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related