Parameterize a query with a set of possible values

  • Ok I swear this will be my last question for a while. I have a query that is generated inside a stored proc with which I pass parameters. Is there anyway to query based on a set of parameters? I have a front end web application that has a web form where the user uses a list and can pick several items from it. I'm not too sure what the output of this is at the moment my guess would be a comma delimited string, generated form the drop down using VB code. I need to somehow pass this into my stored proc so it can return the result set and generate a report.

    The situation would be something like the process below (simplified to get to the point so I'm not going to use a stored Proc in this example)

    -- Test data

    DECLARE @MyTable TABLE

    (

    CityID INT,

    CityName nvarchar(50)

    )

    INSERT INTO @MyTable (CityID,CityName)

    SELECT 1,'Toronto' UNION ALL

    SELECT 2,'Winnipeg' UNION ALL

    SELECT 3,'Vancouver' UNION ALL

    SELECT 4,'Ottawa' UNION ALL

    SELECT 5,'Calgary' UNION ALL

    SELECT 6,'Edmonton' UNION ALL

    SELECT 7,'Charlotte Town'

    -- String to be passed to query from web page

    DECLARE @FrontEndString nvarchar(255)

    SELECT @FrontEndString = '1,3,4'

    -- Actual query to get results

    SELECT

    CityID,

    CityName

    FROM

    @MyTable MT

    WHERE

    CityID IN(@FrontEndString)

    Does this warrant the use of Dynamic SQL somehow? I don't like the idea of dynamic SQL mainly because I have yet to use it, but if there is not other way, perhaps I will try reading up on that. Also, I must give the user a option to select all values.

    Previously I have been using something like the following, where the drop down list to select the city is bound to the table and so returns the correct ID, and I have manually entered another item to the drop down list with ID = -1 called 'All'. A good assumption is that the ID will never be negative so I am justified in using -1.

    WHERE

    (CityID = @CityID OR @CityID = -1)

    Just want to make sure there is no other way to do this first. p.s. I know the above query does not work 🙂

  • In general you'd usually create a function that would break your comma delimited string down into a table for usage. For a local SQL Server Central example: http://www.sqlservercentral.com/scripts/Miscellaneous/31913/

    From there, you'd join the lookup table to the resulting table with something along these lines (pseudocode)

    SELECT

    city

    FROM

    CityLookup AS cl

    JOIN

    fn_StringToTable(@FrontEndString) AS st

    ON cl.id = st.col1


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Is this the only work around? I was hoping for something more straight forward, maybe I'm just expecting too much. This kinda complicates the "All" option as well as it would not make the join.

  • loki1049 (9/2/2010)


    Is this the only work around? I was hoping for something more straight forward, maybe I'm just expecting too much. This kinda complicates the "All" option as well as it would not make the join.

    It's not the only way, just probably the best way. For what is (IMHO) the best string splitting function, click here for the latest Delimited Split Function.

    An alternative is dynamic sql:

    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'

    SELECT

    CityID,

    CityName

    FROM

    @MyTable MT

    WHERE

    CityID IN(' + @FrontEndString + ')';

    EXECUTE (@SQL);

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • You can use charindex too, but convert the string into table is better performance in general.

    EX: select * from @temp where charindex(id,@str) > 0

    Note, don't use this. It is a really bad performance

    WHERE

    (CityID = @CityID OR @CityID = -1)

  • Thanks Wayne, I will look into that post. I don't want to go into dynamic SQL as I don't think it should really be used. I'm not a fan of having code be continally re-written each time the SP is executed. I like concrete things, they tend to sit better with me for some reason.

    And "TheCoding", I know I've seen lots of articles that say this is a bad idea, but I need some default way to select all of a certain ID in the case that one is not given. If you have some other means of doing this let me know.

  • loki1049 (9/2/2010)


    Thanks Wayne, I will look into that post. I don't want to go into dynamic SQL as I don't think it should really be used. I'm not a fan of having code be continally re-written each time the SP is executed. I like concrete things, they tend to sit better with me for some reason.

    And "TheCoding", I know I've seen lots of articles that say this is a bad idea, but I need some default way to select all of a certain ID in the case that one is not given. If you have some other means of doing this let me know.

    CREATE PROCEDURE dbo.GetAllProc AS

    SELECT

    CityID,

    CityName

    FROM

    @MyTable MT

    GO

    CREATE PROCEDURE dbo.GetSomeFromDelimStringProc (@FrontEndString varchar(7999)) AS

    SELECT

    MT.CityID,

    MT.CityName

    FROM

    @MyTable MT

    JOIN dbo.DelimitedSplit8K(@FrontEndString, ',') ds

    ON MT.CityID = ds.Item

    GO

    CREATE PROCEDURE dbo.MainProc (@FrontEndString varchar(7999) = NULL) AS

    IF @FrontEndString IS NOT NULL

    exec dbo.GetAllProc

    ELSE

    exec dbo.GetSomeFromDelimStringProc @FrontEndString

    GO

    The reason for doing it like this is so that each procedure has it's own, non-changing execution plan. This will be better in the long run for you.

    Again, click here for the latest Delimited Split Function.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Wayne, thanks for that example, and yes I am now using that splitter function to create a table to join to. That works well, and I am happy with it, but this whole idea of splitting up the SP into separate cases of querying seems like it might be a PITA for me. You see, in lots of cases, the user wants a choice to filter a view by several different criteria, some of which can be select all or just select one. That is why I have used that parameterized where clause. Below is a typical version of how I would do this:

    SELECT

    Field1ID,

    Field1,

    Field2ID,

    Field2,

    Field3,

    Field4

    FROM

    MyView JOIN Othertables

    ON MyView.asdf = Othertables.asdf

    WHERE

    (DateField BETWEEN @date1 and @date2)

    AND

    (Field1ID = @field1ID OR -1 = @field1ID)

    AND

    (Field2ID = @field2ID OR -1 = @field2ID)

    Something like that, so you say this is bad because of the OR parts or that you should typically not use parameters in the where clause? If I did what you propose wayne, I would have to make 3 different SP for this case to hand all possible out comes, and there might be another SP where I use this -1 logic three times, so thats a whole lotta SP's and makes things quite ugly.

    Thanks for the help so far though!

  • And for what is perhaps the best explanation of the issues this causes, see Gail Shaw's blog[/url]

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • loki1049 (9/2/2010)


    Wayne, thanks for that example, and yes I am now using that splitter function to create a table to join to. That works well, and I am happy with it, but this whole idea of splitting up the SP into separate cases of querying seems like it might be a PITA for me. You see, in lots of cases, the user wants a choice to filter a view by several different criteria, some of which can be select all or just select one. That is why I have used that parameterized where clause. Below is a typical version of how I would do this:

    SELECT

    Field1ID,

    Field1,

    Field2ID,

    Field2,

    Field3,

    Field4

    FROM

    MyView JOIN Othertables

    ON MyView.asdf = Othertables.asdf

    WHERE

    (DateField BETWEEN @date1 and @date2)

    AND

    (Field1ID = @field1ID OR -1 = @field1ID)

    AND

    (Field2ID = @field2ID OR -1 = @field2ID)

    Something like that, so you say this is bad because of the OR parts or that you should typically not use parameters in the where clause? If I did what you propose wayne, I would have to make 3 different SP for this case to hand all possible out comes, and there might be another SP where I use this -1 logic three times, so thats a whole lotta SP's and makes things quite ugly.

    Thanks for the help so far though!

    If you are concerned AT ALL about performance you simply cannot to what you have above.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • loki1049 (9/2/2010)


    I don't want to go into dynamic SQL as I don't think it should really be used. I'm not a fan of having code be continally re-written each time the SP is executed. I like concrete things, they tend to sit better with me for some reason.

    No offense, but it is too bad you have this bias against dynamic sql. It really is a very powerful tool in SQL Server to utilize.

    However, it does get a bad reputation based on people using it incorrectly. But being informed of this you can certainly read up on the measures to take against these things. There is quite a bit of useful info. on this forum about the proper way to go about using dynamic sql. Also an often cited link detailing a great deal is http://www.sommarskog.se/dynamic_sql.html

  • Thanks for all the info guys, and ya, I may have just read too many bad things about dynamic SQL to ever trust it ha. As for the selecting all option, I have decided to just the idea orignally stated, i.e. generating a comma delimited string, using the Parsing function wayne suggested and creating a table to join to. In the case that I want to do a select all type of operation, I will simply give the user an option of all in the list box, then in the code behind when I go to generate the comma delimited string I will just test for the case that All has been selected as an option and load all values from the listbox to be sent as a string and then generated into a table. That way I can just join to that function in the base query, and worry about sending only 1 parameter. I hope this fixes the performance issue you all figure I will run into.

    On a side note, I have been using the WHERE (columnid = @parameter OR -1=@parameter) logic inside a stored proc to query a quite complex view made of several cross joins, derived tables, and a union. When I pass the parameters it takes about 1min 40sec to run. When I try to just query the view by itself i.e. without the (columnid = @parameter OR -1=@parameter), it tends to run even slower. I'm not too sure why...

Viewing 12 posts - 1 through 12 (of 12 total)

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