Using apostrophe in sql statements

Posted on 2014-08-27
Last Modified: 2014-08-27
I get a list of data similar to this  CM,SL,BF

what I need to do is send to parameter in sql stored procedure.

my code looks similar to this.

@myList =   '''WG'',''SM'''

a.propertyid in (@myList)   ''this does not work, however I type

a.propertyid in ('WG','sm') that works and gets the results I need,

So could someone tell me what is wrong with @mylist line and what it needs to be to get this to work.

Question by:mgmhicks
    LVL 39

    Expert Comment

    by:Kyle Abrahams
    the in the @mylist is looking for the literal "WG, SM"

    You need a split function to separate them out:

    a.propertyid in ( select * from dbo.fn_txt_split(@myList, ','))

    Create Function [dbo].[fn_Txt_Split]( 
        @sInputList varchar(8000) -- List of delimited items 
      , @Delimiter char(1) = ',' -- delimiter that separates items 
    RETURNS @list table (Item varchar(8000)) 
    as begin 
    DECLARE @Item Varchar(8000) 
    WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
    IF LEN(@Item) > 0 
    IF LEN(@sInputList) > 0 
    INSERT INTO @List SELECT @sInputList -- Put the last item in 

    Open in new window

    LVL 51

    Expert Comment

    by:Bill Prew
    Try this:

    @myList = "'WG','SM'"


    Author Comment

    sorry that didn't work

    declare @myList nvarchar(50)

    set @mylist =  "'WG','SM"'

    that errors on just typing that.

    Author Comment

    Kyle I tried your function, although it didn't error out It did not give me a results list.  This is what I get when I select @mylist  'WG','SM'   so that looks good but still not getting my results

    here is the entire code.

    declare @myList nvarchar(50)

    set @mylist = '''WG'',''SM'''

    select @mylist

    select a.PropertyId,a.BldgId,a.UnitId,a.ResiId,rtrim(a.propertyid)+'-'+rtrim(a.bldgid)+'-'+rtrim(a.unitid)+'-'+rtrim(a.resiid) as fullID, rtrim(ResiFirstName)+SPACE(1)+rtrim(ResiLastName) as name,LeaseEndDate,resistatus,
    b.Amount,b.Status from a
    right outer join TAG_esite.dbo.ePaySetup b on a.Propertyid=b.PropertyId and a.bldgid=b.BldgId and a.unitid=b.UnitId and a.resiid=b.resiid
    where (LeaseEndDate >='09/01/2014' and Leaseenddate <='12/31/2014' and ResiStatus<>'X') and b.Status='A' and a.PropertyId IN ( select * from dbo.fn_txt_split(@myList, ',')) and LeaseEndDate <>
    (SELECT DATEADD(dd, -DAY(DATEADD(m,1,leaseenddate)), DATEADD(m,1,LeaseEndDate))
                AS LastDayOfMonth)
    order by a.leaseenddate

    thank you for the help
    LVL 13

    Expert Comment

    by:Russell Fox
    You can try this with the LIKE operator, instead of splitting them:
    @myList = '%WG%SM%'
    a.propertyid LIKE @myList

    Open in new window

    LVL 39

    Accepted Solution

    No need for apostrophes.

    set @mylist = 'WG,SM'

    Author Comment

    when I do a select * from dbo.fn_txt_split(@myList, ',') I get this


    so that's 2 records,

    Author Closing Comment

    Awesome solution, thank you very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now