CODEWITHSUNDEEP
sql-serversql-server-2005stored-procedures
TheMethod
TheMethod

Reputation: 3001

Nested If in stored proc causing syntax error

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!

Upvotes: 0

Views: 128

Answers (2)

Taryn
Taryn

Reputation: 247810

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

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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

Upvotes: 1

Related Questions