asked on

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

Kyle Abrahams, PMP

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

Bill Prew

Try this:

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

~bp

mgmhicks

ASKER

sorry that didn't work

declare @myList nvarchar(50)

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

that errors on just typing that.

mgmhicks

ASKER

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

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

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mgmhicks

ASKER

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

Item
'WG'
'SM'

so that's 2 records,

mgmhicks

ASKER

Awesome solution, thank you very much