Stored Procedures (SQL Server)

• Javier-197332

  Newbie

  Points: 1

  More actions

  December 22, 2004 at 7:49 am

  #60580

  Hello, I've got a problem that i don't know if it has a simple solution but i hope it has and someone can help me. The problem's the next: I have a stored procedure with a SELECT query and i need to send as parameter all the conditions (that i have as a string in my application) to filter the rows.

  Example:

  CREATE Procedure viewClients

  @Filter varchar(200)

  AS

  SELECT * FROM Clients

  WHERE @Filter

  Return

  WHERE i will send a string like next to @Filter:

  - Age > 20

  - Name LIKE 'J%' AND Sex = 'Female'

  - InDate BETWEEN #1/1/2004# AND #31/12/2004#

  Or anything the user wants

  I don't know if you understand what i want to do, but sql doesn't understand, i get an error at line "WHERE @Filter". Is there any way to do that?

  Thanks

  Javier

• sa24

  SSCrazy Eights

  Points: 8027

  More actions

  December 22, 2004 at 7:52 am

  #534758

  You can do the below.

   

  declare @where varchar(100)

  set @where = 'name = ''sysobjects'''

  declare @sql varchar(200)

  set @sql = 'select * from sysobjects where ' + @where

  exec(@sql)

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  December 23, 2004 at 1:38 am

  #534861

  IF you want to try out the suggested solution, be sure to read this http://www.sommarskog.se/dynamic_sql.html before you decide if it's a good idea or not to use dynamic SQL.

  There are other ways than the above to solve this kind of problem.

  eg change the proc to a view instead and build the WHERE at the client - then have the client do a select against the view instead of calling a proc. No dynamic SQL, no problems, security can still be granted to an object instead of the base tables.

  /Kenneth