When using stored procedures in Microsoft SQL Server there are times when the SQL that needs to be executed, needs to be different depending upon one of the input parameters of the stored procedures. For example lets say you have a procedure that returns a result set and you need to return different tables depending upon the provided parameter.
I’ve created a simple sample in which I’ve created a stored procedure and passed the name of the database table that I want to execute. This is just for demonstration purposes and you can (mostly) use the branching statements of T-SQL for creating the SQL to be executed inside the stored procedure. The execute statement in SQL Server can take the full SQL statement and will execute that on your behalf. Also you need to have proper permissions for the database table that you are executing or the statement will not execute. Here is the code for the stored procedure.
Create Procedure dbo.TestProc1
(
@tableName varchar(100)
)
As
EXECUTE('select * from ' + @tableName);
Go
Grant Execute On dbo.TestProc1 To public
exec dbo.TestProc1 'Employees'
