Stored Procedure with dynamic where condition

Question

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

Anupam Singh · Answer

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 Trivedi · Answer

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

Stored Procedure with dynamic where condition

Answers (2)