Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

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

In this article I’ll try to explain how you can execute a different SQL statement depending upon an entered parameter in Microsoft SQL Server

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'

Article Comments
nice tutorial

Posted on 3/21/2008 4:58:41 AM by rathnakar

it's very nice

Posted on 4/18/2008 11:49:04 AM by vinay

very good

Posted on 4/29/2008 9:43:38 AM by Rajendra

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create a Store Procedure in SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net