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
I Learnt Today 