top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to dynamically change the executing SQL in a stored procedure in Microsoft SQL Server?

+1 vote
410 views
How to dynamically change the executing SQL in a stored procedure in Microsoft SQL Server?
posted Mar 29, 2016 by Sathyasree

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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'
answer Mar 29, 2016 by Shivaranjini
...