Stored Procedure with dynamic where condition

Marutharasu P

12y1.3k

I need to write stored procedure with dynamic where condition for filtering some records. The filter parameters are given by the front end application.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anupam Singh12y ago
Hi,
you can create procedure with parameter ex:

CREATE PROCEDURE GetAddress @City varchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

and you can execute it using :

EXEC GetAddress @City = 'New York'

You just need to change city in parameter.

in C# you can achieve this using:

connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetAddress ";

param = new SqlParameter("@City", "New York");
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);

adapter = new SqlDataAdapter(command); //get data in dataadapter
adapter.Fill(ds); //fill data set

Hope will help.
Jignesh Trivedi12y ago
Hi,
To do this, I think you have to write dynamic query.
like
Create proc Test
( @where varchar(max)
)
AS
BEGIN
declare @sql varchar(max)
set @sql = 'select * from tablename where ' + @where
exec(@sql)
END
also refer
http://www.codeproject.com/Articles/21234/Implementing-Dynamic-WHERE-Clause-in-Static-SQL
hope this will help you