Using data to dynamically generate SQL Select Statement.

Question

Using data to dynamically generate SQL Select Statement.

allen kwok

Valued Member

Points: 58
More actions
January 10, 2007 at 4:38 pm

#68173

Scenario:
A user is able to do a search for other users in the database based on certain criteria, such as their City and likes / dislikes. This list of filters can change and multiple selections are allowed. These filters are then saved to the database and are used whenever the user wishes to view their custom search. The results of this search can grow or shrink depending on users that are added / removed from the system.
I need to use the filter data stored in the database to dynamically generate a select statement that will return the correct results for the search.
Are there any ideas / resources available on how this can be accomplished?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Stéphane SSC-Addicted Points: 470 More actions · Answer 1

I'm not sure I understand, but what about building your SQL string from the criterias stored in the DB and then execute the statement?

DECLARE

@sqlString nvarchar(4000)

SET @sqlString = 'select ' + param1 + ', ' + param2 + ' from tbl_x where ' ...

exec sp_executesql @sqlString

allen kwok Valued Member Points: 58 More actions · Answer 2

Thanks for your reply Stephane,

I will indeed be using the execute statement for this, but the main problem I am having is how to effectively build the statement I want to execute.

Say for example one user has 3 different cities he wants to filter on, and another has 20 different cities he wants to filter on. And then another user doesn't want to filter on cities but wants to filter by ages 20-29 within a distance of 200 miles. All this is stored in the database for later use.

SELECT user.*

FROM User JOIN '*dyamically determine tables to join based on what user selected*'

WHERE '*dynamically determine filters based on what user selected*'

I need to able to generate the statement to handle all these cases without uncessary joins on tables that I dont need. I can't seem to find a good way of doing this without it being extremely messy and unextensible.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 3

I recently worked up a solution not unlike what you are describing. Essentially, I created a stored procedure that allows the user to pass a comma-separated list for each search parameter. I created a UDF that processes the multivalued parameters within the sproc, and proceeded from there.

If you're interested, ping me and I'll post some code samples of what I came up with.

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices