Using Dynamic SQL Statements in Stored Procedures

SQL contains a command, EXEC (or EXECUTE), which can be used to dynamically execute a SQL statement. EXEC expects a string - the SQL statement you wish to execute. It will execute the SQL statement passed and returns the results.

Example:

CREATE PROCEDURE SP_GETELEMENTVALUESFORID
@ELEMENT varchar(100),
@EMPLOYEEID varchar(100)
AS
DECLARE @QUERY varchar(250)

IF(@ELEMENT = 'LOCATION')
SET @QUERY = 'SELECT DISTINCT LOCATION, TLOC.LOCATIONID From TBLLOCATION TLOC JOIN TBLACCESS TACC ON TLOC.LOCATIONID = TACC.LOCATIONID WHERE TACC.EMPLOYEEID = ' + @EMPLOYEEID
ELSE IF(@ELEMENT = 'VERTICAL')
SET @QUERY = 'SELECT DISTINCT VERTICAL, TVER.VERTICALID From TBLVERTICAL TVER JOIN TBLACCESS TACC ON TLOC.VERTICALID = TACC.VERTICALID WHERE TACC.EMPLOYEEID = ' + @EMPLOYEEID

EXEC (@QUERY)
GO

Do not Forget the bracket surrounding the @query. Without the brackets, you will get an error in when executing.