Adhoc Query in SP, best way

• Steve Dingle

  SSChasing Mays

  Points: 601

  More actions

  December 16, 2006 at 11:16 am

  #114888

  Hello All,

  I am creating an SP(s) which will accept a WHERE clause as a parameter and generate a result set. My current approach has been something like:

  CREATE PROCEDURE dbo.AdhocQuery

  (@WhereClause varchar(1000))

  AS

  SET NOCOUNT ON

  DECLARE @sqlquery AS nvarchar(2000)

  SET @sqlquery = N'SELECT fields'

  +' FROM dbo.Table'

  +' JOIN dbo.Othertable'

  +' ON Othertable.pk = Table.fk'

  +' WHERE '+ @WhereClause

  EXECUTE sp_executesql @sqlquery

  I was wondering if this is the best approach.

  For a little history... I have an application with a front end which will generate a WHERE clause based on what a user selects. Often the user will have up to 6 tables with 10 fields each to build their filter so I can't imagine building an SP which would allow me to send in the individual pieces and have the SP "build" the where clause as I will never know how many pieces.

  Any thoughts or input would be greatly appreciated.

  ---

  Much Thanks,

  Steve Dingle

• Eric Cogen-316428

  SSC Enthusiast

  Points: 165

  More actions

  December 16, 2006 at 1:27 pm

  #678525

  I personally would not do that, best approach would be to use specific parameters and construct your where clause in your proc. building adhoc sql outside of a stored procedure, your just inviting danger. Better yet, build a view on the table and select from that based on a conditional where clause and set select only permissions on the underlying table for the web_user login.

• Steve Dingle

  SSChasing Mays

  Points: 601

  More actions

  December 16, 2006 at 2:18 pm

  #678529

  Thanks for the response, I will talk to my client about limiting what the user can filter to give use that option but I don't think that will fly for now.

  A lot of the users are doing analysis type work and they need the capabilty to search for what they choose as needed for the job at hand. They have had problems in the passed with users not being able to get at the data they needed and having to wait for a IT dept to get it for them. Didn't work at all, too long a turn around and often resutling with the IT dept telling them what they needed to do their jobs, nobody appreciates that 😉

  We are talking about data warehousing for the future where we might be able to limit the filtering but for now they need to be able to create filters which include BETWEEN and AND/OR, NOT IN etc... FWIW, the GUI app creates the WHERE clause based on users selection in a friendly form (i.e. user don't need to know SQL).

  ---

  Much Thanks,

  Steve Dingle

• carie dobson

  SSC Enthusiast

  Points: 178

  More actions

  December 18, 2006 at 1:11 pm

  #678790

  Steve,

  Something that has worked for me in the past is doing this:

  CREATE Proc ProcName

  @Parm1 varchar(50) = Null

  AS

  SELECT Field1, Field2...

  FROM dbo.TableName

  WHERE Field1 = IsNull(@Parm1, Field1)

  GO

  If a param is passed it is used to filter the results, if not, then it returns "true" when comparing the field to itself.

  Don't know if that will work for your situation, but thought I'd mention it.  Have a great day.

  Carie