Using apostrophe in sql statements

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.

thanks
mgmhicksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
the in the @mylist is looking for the literal "WG, SM"

You need a split function to separate them out:

where
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 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

Open in new window

0
Bill PrewCommented:
Try this:

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

~bp
0
mgmhicksAuthor Commented:
sorry that didn't work

declare @myList nvarchar(50)

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

that errors on just typing that.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

mgmhicksAuthor Commented:
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 tag_esite.dbo.lease 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
0
Russell FoxDatabase DeveloperCommented:
You can try this with the LIKE operator, instead of splitting them:
@myList = '%WG%SM%'
a.propertyid LIKE @myList

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
No need for apostrophes.

set @mylist = 'WG,SM'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mgmhicksAuthor Commented:
when I do a select * from dbo.fn_txt_split(@myList, ',') I get this

Item
'WG'
'SM'

so that's 2 records,
0
mgmhicksAuthor Commented:
Awesome solution, thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.