locked
How i pass multiple values into the where condition column ? RRS feed

  • Question

  • Hi,

    I have a query and want to pass multiple column values as a parameter.

    for example i have below qquery where i want to pass multiple values instead of one value-

    SELECT DISTINCT prs.RiderId

    FROM [dbo].[PlanRiderSet] prs

    INNER JOIN [dbo].[PlanProductSet] pps

    ON prs.PlanId = pps.PlanId

    inner join [dbo].[PlanRider] pr

    on pr.Id=prs.RiderId

    WHERE pr.RiderIdNumber=604 –Here I pass value like multiple means ‘604,601,605,703’ like this values are in table column values.

    and pps.ProductId = (SELECT pps.ProductId

    FROM [dbo].[CustomerXref] cxref

    INNER JOIN [dbo].[CustomerPlanXref] cpxref

    ON cpxref.Id = cxref.PlanXrefId

    INNER JOIN [dbo].[Plan] p

    ON p.Id = cpxref.PlanId

    INNER JOIN [dbo].[PlanProductSet] pps

    ON p.Id = pps.PlanId

    WHERE cxref.PlanXrefId = 3598)

    ORDER BY prs.RiderId

    HOW i ACHIEVE THIS!!!

    Friday, February 8, 2013 9:30 AM

Answers

  • Try below query ,

    SELECT DISTINCT prs.RiderId
    
    FROM [dbo].[PlanRiderSet] prs
    
    INNER JOIN [dbo].[PlanProductSet] pps
    
    ON prs.PlanId = pps.PlanId
    
    inner join [dbo].[PlanRider] pr
    
    on pr.Id=prs.RiderId
    
    WHERE pr.RiderIdNumber in (604,601,605,703)
    
    and pps.ProductId = (SELECT pps.ProductId
    
    FROM [dbo].[CustomerXref] cxref
    
    INNER JOIN [dbo].[CustomerPlanXref] cpxref
    
    ON cpxref.Id = cxref.PlanXrefId
    
    INNER JOIN [dbo].[Plan] p
    
    ON p.Id = cpxref.PlanId
    
    INNER JOIN [dbo].[PlanProductSet] pps
    
    ON p.Id = pps.PlanId
    
    WHERE cxref.PlanXrefId = 3598)
    
    ORDER BY prs.RiderId



    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Marked as answer by Maggy111 Friday, February 8, 2013 10:25 AM
    Friday, February 8, 2013 9:45 AM
  • If it is part of a procedure where you get the list as parameter, you may need a dynamic SQL to accomplish the same. Or you may even try the list to split to atable and join with the table.

    If it is just a query, then Rakesh's query would do the magic for you.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Maggy111 Friday, February 8, 2013 10:25 AM
    Friday, February 8, 2013 9:52 AM

All replies

  • Try below query ,

    SELECT DISTINCT prs.RiderId
    
    FROM [dbo].[PlanRiderSet] prs
    
    INNER JOIN [dbo].[PlanProductSet] pps
    
    ON prs.PlanId = pps.PlanId
    
    inner join [dbo].[PlanRider] pr
    
    on pr.Id=prs.RiderId
    
    WHERE pr.RiderIdNumber in (604,601,605,703)
    
    and pps.ProductId = (SELECT pps.ProductId
    
    FROM [dbo].[CustomerXref] cxref
    
    INNER JOIN [dbo].[CustomerPlanXref] cpxref
    
    ON cpxref.Id = cxref.PlanXrefId
    
    INNER JOIN [dbo].[Plan] p
    
    ON p.Id = cpxref.PlanId
    
    INNER JOIN [dbo].[PlanProductSet] pps
    
    ON p.Id = pps.PlanId
    
    WHERE cxref.PlanXrefId = 3598)
    
    ORDER BY prs.RiderId



    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Marked as answer by Maggy111 Friday, February 8, 2013 10:25 AM
    Friday, February 8, 2013 9:45 AM
  • If it is part of a procedure where you get the list as parameter, you may need a dynamic SQL to accomplish the same. Or you may even try the list to split to atable and join with the table.

    If it is just a query, then Rakesh's query would do the magic for you.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Maggy111 Friday, February 8, 2013 10:25 AM
    Friday, February 8, 2013 9:52 AM
  • You can use IN with where....

    SELECT DISTINCT prs.RiderId

    FROM [dbo].[PlanRiderSet] prs

    INNER JOIN [dbo].[PlanProductSet] pps

    ON prs.PlanId = pps.PlanId

    inner join [dbo].[PlanRider] pr

    on pr.Id=prs.RiderId

    WHERE pr.RiderIdNumber IN (‘604,601,605,703’) 

    and pps.ProductId = (SELECT pps.ProductId

    FROM [dbo].[CustomerXref] cxref

    INNER JOIN [dbo].[CustomerPlanXref] cpxref

    ON cpxref.Id = cxref.PlanXrefId

    INNER JOIN [dbo].[Plan] p

    ON p.Id = cpxref.PlanId

    INNER JOIN [dbo].[PlanProductSet] pps

    ON p.Id = pps.PlanId

    WHERE cxref.PlanXrefId = 3598)

    ORDER BY prs.RiderId


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, February 8, 2013 11:23 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.
     
    >> I have a query and want to pass multiple column values as a parameter. <<

    This makes no sense!! Queries do not have parameters; procedures have parameters. Parameters are scalar values of a known data type. If this was a geography class, you would be the kid who thinks the Earth is flat.

    The simplest answer is to use a long parameter list to construct lists and derived tables inside the procedure body. SQL server can handle up to 2100 parameters, which should be more than enough for practical purposes. I have a two articles on this topic at Simple Talk;

    http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

    http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/

    Also, the little bit of code you did post is awful. Not bad, awful. You truly have no idea how to use SQL or to program properly.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, February 8, 2013 6:37 PM
  • WHERE MyValue IN (SELECT Blah FROM pcpFnSplit(@MyCSVParameter,','))

    The above is the most practical way of doing it - use a practical,  punchcard programmer style Split function that returns a table of values, and throw that into IN().

    Now before Joe sends me off to purgatory for using UDF's... you can use his parameter lists if you wish... but the split is so much easier to do.

    (And if you have portability issues with the above you could always ask Joe to help port it :D )




    Thanks! Josh Ash


    Monday, February 11, 2013 2:21 AM
  • >> Now before Joe sends me off to purgatory for using UDF's... you can use his parameter lists if you wish... but the split is so much easier to do. <<

    No, it is not easier, if you do it right. When you pass a parameter, the compiler will do a lot of work for you (type checking, truncation detection, missing parameters, etc.)  When you get lazy and use a splitter you lose all of that  (see my articles). If you are a good programmer and still want to use a splitter, then you have to write code to do those tasks in T-SQL.

    More than that, the RAISERROR messages that your code sends back should match the messages from the T-SQL compiler. This will let calling modules detect errors and take proper actions.

    And we have not talked about SQL injection yet! Lazy, sloppy coding is so much easier. But it is not worth it.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, February 12, 2013 12:03 AM
  • HI.,

    In will work for multiple condition...

    SELECT DISTINCT prs.RiderId

    FROM [dbo].[PlanRiderSet] prs

    INNER JOIN [dbo].[PlanProductSet] pps

    ON prs.PlanId = pps.PlanId

    inner join [dbo].[PlanRider] pr

    on pr.Id=prs.RiderId

    WHERE pr.RiderIdNumber in(604,605,606) and pps.ProductId = (SELECT pps.ProductId

    FROM [dbo].[CustomerXref] cxref

    INNER JOIN [dbo].[CustomerPlanXref] cpxref

    ON cpxref.Id = cxref.PlanXrefId

    INNER JOIN [dbo].[Plan] p

    ON p.Id = cpxref.PlanId

    INNER JOIN [dbo].[PlanProductSet] pps

    ON p.Id = pps.PlanId

    WHERE cxref.PlanXrefId = 3598)

    ORDER BY prs.RiderId

    Regards

    Ganesh


    Tuesday, February 12, 2013 6:10 AM
  • I take your points there Joe and am familiar with your articles on this subject...

    But I'm yet to run into any issues using split functions.. and the amount of extra time it would take both to implement and maintain long parameter lists isn't justified in my case by the benefits it brings.. a lot of the time as you'd understand there is a requirement to step back from the ideal solutions to practical ones for financial/time reasons.

    Using split functions this way is pretty much a standard now.. I haven't seen an implementation of long parameter lists as you advocate them out in the field.

    Besides, I'm guilty of greater portability and design sins than split functions...


    Thanks! Josh Ash

    Friday, February 15, 2013 4:39 AM