Post reply

Need Help on T-Sql Tricky Query.

  • June 3, 2011 at 8:33 am

    #389654

    Hi Guys,

    I need help on below requirment.i am using Sqlserver 2008,SSRS 2008.

    Actvually i have a report which will display the information of customer info based on the canceld orders.

    I have 2 parameters parameter @customerId and @CustomerName.

    Parameter @CustomerName=FirstName + Middlename + LastName.

    i used the below query to get the customer info.

    Select id,FirstName + Middlename + LastName as Customername,type from CustOrders

    Where id In (@customerId) And FirstName + Middlename + LastName Like ('@CustomerName')

    The query working fine when i use it for single value but i have use it for multiple values i am using belo one it throwing below exception.

    Select id,FirstName + Middlename + LastName as Customername,type from CustOrders

    Where id In (1,2,3) and FirstName + Middlename + LastName In ('A,B,C,D')

    Thanks & Regards,
    Veeren.
    Ignore this if you feel i am Wrong. 😉

  • June 3, 2011 at 9:08 am

    #1333750

    The IN operator needs distinct values, ie. IN (1,2,3,4) or IN ('A','B','C','D').

    When they are combined in one variable, it is not expanded out.

    You'll need to either split them out, or utilize dynamic sql and the sp_executesql statement to build a sql string to execute.

    Here is the latest version of the [http://www.sqlservercentral.com/articles/Tally+Table/72993/]Delimited Split Function[/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

  • June 3, 2011 at 9:23 am

    #1333760

    Okay, I'm looking at this and thinking there's a bigger issue here.

    Why do you have multiple customers (different names) with the same ID number? Wouldn't customer ID alone be enough to identify a customer?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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