How to use DECLARE in a stored procedure

Question

I am creating a stored procedure that will create a new row in a table once the record is new and update the existing record if the record is present in the table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [procedurename]
    (@UserName [varchar](50),
     @UserEmail [varchar](50),
     @Role [varchar](30),
     @Status [varchar](30),
     @CreationDate [Date])
AS
    DECLARE @countRec INT

    SELECT @countRec = COUNT(*) 
    FROM [Synapse].[tablename] 
    WHERE [UserName] = @Username

BEGIN
    IF (@countRec = 0)
    BEGIN
        INSERT INTO [Synapse].[tablename] ([UserName], [UserEmail], [Role], [Status], [CreationDate])
        VALUES (@Username, @UserEmail, @Role, @Status, @CreationDate)
    END
    ELSE (@countRec > 0)
    BEGIN
        UPDATE [Synapse].[tablename]
        SET [Role] = @Role, 
            [Status] = @Status, 
            [CreationDate] = @CreationDate 
        WHERE [UserName] = @Username

        INSERT INTO [Synapse].[tablename]
    END

And I am getting the following error:

Msg 103010, Level 16, State 1, Line 8
Parse error at line: 39, column: 7: Incorrect syntax near '@countRec'.

I am not sure why it is pointing at the countRec variable. I was able to use a similar set-up without any issues. Does anyone know what I am doing wrong here?

Kind regards,

Rutger

Answer 1

You have a number of syntax errors - mostly: you need to start your proedure with a BEGIN before doing any declarations or selects - and you're missing an END at the end.

Try this:

CREATE PROCEDURE [procedurename]
    (@UserName [varchar](50),
     @UserEmail [varchar](50),
     @Role [varchar](30),
     @Status [varchar](30),
     @CreationDate [Date])
AS
BEGIN    -- put the "BEGIN" before you start declaring and selecting stuff
    DECLARE @countRec INT

    SELECT @countRec = COUNT(*) 
    FROM [Synapse].[tablename] 
    WHERE [UserName] = @Username

    IF (@countRec = 0)
    BEGIN
        INSERT INTO [Synapse].[tablename] ([UserName], [UserEmail], [Role], [Status], [CreationDate])
        VALUES (@Username, @UserEmail, @Role, @Status, @CreationDate)
    END
    -- either you have just an "ELSE" here - or if you want to 
    -- check another conditions, you need to use "ELSE IF ....."
    ELSE IF (@countRec > 0)
    BEGIN
        UPDATE [Synapse].[tablename]
        SET [Role] = @Role, 
            [Status] = @Status, 
            [CreationDate] = @CreationDate 
        WHERE [UserName] = @Username
    END
END   -- this was missing