Dynamic Stored Procedures

you may want a stored procedure that takes, as an input, a particular WHERE clause, for example in search such that your stored procedure could be defined as:
CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
AS
SELECT *
FROM TableName
WHERE @WHEREClause

Or perhaps you’d like to be able to query a particular table based upon a parameter, like:
CREATE PROCEDURE MyProc
(@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50))
AS
SELECT *
FROM @TableName
WHERE FirstName = @FirstName
AND LastName = @LastName

In either case, if you try putting either of the above code snippets into a stored procedure, you’ll get an error. To execute a dynamic SQL statement in a stored procedure, you need to use the EXEC function. The EXEC function takes a SQL string as a parameter, and executes that SQL statement.

So, when using the EXEC function, begin by declaring a varchar(255) variable named @SQLStatement. Then, assign your dynamic SQL statement to this variable, and, finally, use EXEC to execute the SQL statement! For example, the first example above should be changed to:
CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM TableName WHERE "
+ @WHEREClause

-- Execute the SQL statement
EXEC(@SQLStatement)

The second example could be changed to:
CREATE PROCEDURE MyProc
(@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50))
AS

— Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

— Enter the dynamic SQL statement into the
— variable @SQLStatement
SELECT @SQLStatement = “SELECT * FROM ” +
@TableName + “WHERE FirstName = ‘”
+ @FirstName + “‘ AND LastName = ‘”
+ @LastName + “‘”

— Execute the SQL statement
EXEC(@SQLStatement)

Note that you have to surround the value of @FirstName and @LastName with single quotes, much like you do when building a SQL statement in an ASP page. Also note that if @FirstName or @LastName contain single quotes, an error will occur. Therefore, you should Replace the single quotes with two single quotes in your ASP page before calling the stored procedure.

Advertisements

~ by UTS on May 26, 2009.

One Response to “Dynamic Stored Procedures”

  1. good work Tehmina

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: