A stored procedure that can auto-genarate SQL queries using exec method

06-10-2011

http://pagead2.googlesyndication.com/pagead/show_ads.js

Have you ever wanted to write a flexible and dynamic stored procedure that would allow you to send the table name as a parameter?

Have you ever wanted to write a flexible and dynamic stored procedure that would allow you to send only the condition but also the column name you want to include in the where clause?

Well I did. I wanted to create a stored procedure that would take Table name, Criteria column name  and the Target criteria value as parameters and create the SQL query dynamically.

Usually, we have to write our SQL code in the stored procedure body where we cannot treat our sql statement as a string.

The way to achieve this, however, is by using the Exec method provided in SQL.

Exec (execute) allows you to execute a command or a character string that contains Transact-SQL command(s).

Without wasting more time, following is the code I used to achieve my goal:

CREATE PROCEDURE GetRowCountByStringColumn

@TableName nvarchar(50),
@CriteriaColumnName nvarchar(50),
@CriteriaValue nvarchar(150)
AS
BEGIN

EXEC('SELECT  Count(*) FROM ' + @TableName
+ ' WHERE ' + @CriteriaColumnName + ' = ''' + @CriteriaValue + '''')

END
GO

As you can see the procedure takes three arguments:

TableName
CriteriaColumnName – the name of the column that should be checked in the where clause
CriteriaValue – the value the Criteria Column Name should be checked against

The exec command creates the SQL command (at run time) and executes it.

This helped me to create many dynamic stored procedures for my current project and saved me from having to create stored procedures for each table.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: