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?

Don't name your stored procedures with the "sp_" prefix on SQL Server. — pmbAustin
– pmbAustin, Commented Feb 1, 2017 at 15:45
The "sp_" prefix is used by the system for system procedures that are stored in the master database. It causes a look-up for the stored procedure in master first, and then in your current database second, so it's a performance hit. Here are some details: sqlperformance.com/2012/10/t-sql-queries/sp_prefix — pmbAustin
– pmbAustin, Commented Feb 1, 2017 at 15:49

marc_s · Accepted Answer · 2017-02-01 17:00:39Z

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?

pmbAustin · Accepted Answer · 2017-02-01 15:46:39Z

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

Simon · Accepted Answer · 2017-02-01 15:46:56Z

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

Collectives™ on Stack Overflow

IF and ELSE inside stored procedure

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related