IF and ELSE inside stored procedure

Question

I am trying to put an IF/ELSE statement into a stored procedure.

CREATE PROCEDURE [dbo].[Sp_enty_srch_attr_vals] 
    (@ENTY_ID  INT,
     @FIELD_ID INT,
     @VALUE VARCHAR(5000),
     @SQL VARCHAR(5000)
    )
AS
BEGIN
    IF (@FIELD_ID = 148)
        SET nocount ON;

        SET @SQL = 'SELECT DISTINCT [GPI] FROM [GPI_ASOC] WHERE [GPI] LIKE ''%@VALUE%'' ORDER BY GPI';
        SET @SQL = replace(@SQL, '@VALUE', @VALUE);

        EXEC SP_EXECUTESQL @SQL;

    ELSE
        SET nocount ON;
        DECLARE @ENTY_TABLE_NAME VARCHAR(500)

        SET @ENTY_TABLE_NAME = (SELECT TOP 1 enty_table_name
                                FROM enty
                                WHERE enty.enty_id = @ENTY_ID)

        EXEC( 'SELECT DISTINCT ATTR_VAL  FROM '+@ENTY_TABLE_NAME+'_ATTR WHERE FIELD_ID='+@FIELD_ID+
      ' AND ATTR_VAL LIKE ''%'+@VALUE+'%'' ORDER BY ATTR_VAL ' )
END

I am getting this error and I have been banging my head against the wall trying to fix it.

Msg 156, Level 15, State 1, Procedure Sp_enty_srch_attr_vals, Line 14 [Batch Start Line 17]
Incorrect syntax near the keyword 'ELSE'.

What is wrong with this script?

Answer 1

When you have more than one statement in an IF condition, then you need to use a BEGIN-END block

If <<condition>>
BEGIN 
  <<stmt 1>>
  <<stmt 2>>
END
ELSE
BEGIN 
  <<stmt 3>>
  <<stmt 4>>
END

Also do not prefix sp_ to your stored procedure. All the system stored procedures will be prefixed with sp_. Here is the articles about the problem

Should I Use the sp_ Prefix for Procedure Names?

Is the sp_ prefix still a no-no?

Answer 2

You need BEGIN and END to bracket your code in the body of your IF/ELSE. You can also move the SET NOCOUNT ON to outside the IF:

CREATE PROCEDURE [dbo].[Enty_srch_attr_vals] (@ENTY_ID  INT,
                                              @FIELD_ID INT,
                                              @VALUE    VARCHAR(5000),
                                              @SQL      VARCHAR(5000))
AS
BEGIN
    SET nocount ON;
      IF( @FIELD_ID = 148 )
      BEGIN
        SET @SQL = 'SELECT DISTINCT [GPI] FROM [GPI_ASOC] WHERE [GPI] LIKE ''%@VALUE%'' ORDER BY GPI';
        SET @SQL = replace(@SQL, '@VALUE', @VALUE);
        EXEC SP_EXECUTESQL @SQL;
      END
      ELSE
      BEGIN
        DECLARE @ENTY_TABLE_NAME VARCHAR(500)
        SET @ENTY_TABLE_NAME = (SELECT TOP 1 enty_table_name
                                FROM   enty
                                WHERE  enty.enty_id = @ENTY_ID)

        EXEC( 'SELECT DISTINCT ATTR_VAL  FROM '+@ENTY_TABLE_NAME+'_ATTR WHERE FIELD_ID='+@FIELD_ID+
      ' AND ATTR_VAL LIKE ''%'+@VALUE+'%'' ORDER BY ATTR_VAL ' )
      END
END

go  

Answer 3

For multi-line IF/ELSE statements you should use a BEGIN/END block, eg.

  IF( @FIELD_ID = 148 )
   BEGIN
    SET nocount ON;
    SET @SQL = 'SELECT DISTINCT [GPI] FROM [GPI_ASOC] WHERE [GPI] LIKE ''%@VALUE%'' ORDER BY GPI';
    SET @SQL = replace(@SQL, '@VALUE', @VALUE);
    EXEC SP_EXECUTESQL @SQL;
   END