• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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
0
mgmhicks
Asked:
mgmhicks
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now