Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-05 : 09:59:58
|
Hi all,In my stored procedure I have an incoming array of strings (e.g. 'ECSO10ARR', ECSO11ARR', ECSO12ARR').ALTER PROCEDURE [dbo].[specso_WarrantsByZone_Report] @yearval VARCHAR(500),AS I would like to loop through this list and then build a string which will serve as a condition in a WHERE clause, like this.WHERE yearfield LIKE 'ECSO10ARR%' OR yearfield LIKE 'ECSO11ARR%' OR yearfield LIKE 'ECSO12ARR%'I'm really stumped with this, so I would greatly appreciate your assistance.Thank you. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-02-05 : 10:28:26
|
OR -choose your favorite table valued "split function" (there are countless examples posted here on this site) and JOIN to it something like this:from dbo.fn_splitFunction(@yearval, ',') fjoin [yourTable] yt on yt.yearfield like f.output + '%'Be One with the OptimizerTG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 10:55:14
|
in your case it would be likeALTER PROCEDURE [dbo].[specso_WarrantsByZone_Report]@yearval VARCHAR(500),AS SELECT FROM (SELECT CAST('<Node><Element>' + REPLACE(@yearval,',','</Element><Element>') + '</Element></Node>' AS xml) AS XMLval) tINNER JOIN yourtable t1ON XMLval.exist('/Node/Element[.=sql:column("yearfield")]')=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-05 : 11:25:47
|
Thanks guys for your help. The problem is that it's a little more involved. How would I add that logic to a query already containing JOINS amd multiple conditions, such as:SELECT NAME FROM MBI..MBI WITH (NOLOCK), MBI..MBI_ADD WITH (NOLOCK), ART..ART WITH (NOLOCK), ART..ARR_AFSS WITH (NOLOCK) WHERE ART.ARTTYPE = 'W' AND ART.WARSTATUS = 'W' AND AND MBI_ADD.ECSOID = MBI.ECSOID AND ART.ECSOID = MBI.ECSOID AND ART.ARTNO = ARR_AFSS.ARTNO AND //ADD CONDITION FOR YEAR VALUE HEREThanks for your patience with me and your help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 12:08:45
|
[code]SELECT NAMEFROM MBI..MBI WITH (NOLOCK)INNER JOIN MBI..MBI_ADD WITH (NOLOCK)ON MBI_ADD.ECSOID = MBI.ECSOIDINNER JOIN ART..ART WITH (NOLOCK)ON ART.ECSOID = MBI.ECSOIDINNER JOIN ART..ARR_AFSS WITH (NOLOCK)ON ART.ARTNO = ARR_AFSS.ARTNOINNER JOIN (SELECT CAST('<Node><Element>' + REPLACE(@yearval,',','</Element><Element>') + '</Element></Node>' AS xml) AS XMLval) tON XMLval.exist('/Node/Element[.=sql:column("Youryearfieldhere")]')=1WHERE ART.ARTTYPE = 'W' AND ART.WARSTATUS = 'W' [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-05 : 13:08:45
|
Thank so much Vis. Does your select cast handle LIKE? My incoming values will be like ECSO12ARR, ECSO13ARR but I need to search for anything that begins with that: 'ECSO12ARR%' OR 'ECSO13ARR%'.Also, "Youryearfieldhere" refers to the database filed, correct? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-02-05 : 13:24:02
|
>> Does your select cast handle LIKE?The solution I posted does.Be One with the OptimizerTG |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-05 : 14:23:19
|
I see that TG, and I appreciate it tremendously. Would you know how to incorporate your FROM clause in my big SELECT?Thank you. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-02-05 : 15:31:18
|
Something like this. (I changed your old style join syntax to ANSI compliant)SELECT NAMEFROM MBI..MBI WITH (NOLOCK)inner join MBI..MBI_ADD WITH (NOLOCK) on MBI_ADD.ECSOID = MBI.ECSOIDinner join ART..ART WITH (NOLOCK) on ART.ECSOID = MBI.ECSOID inner join ART..ARR_AFSS WITH (NOLOCK) on ARR_AFSS.ARTNO = ART.ARTNO //ADD CONDITION FOR YEAR VALUE HEREinner join dbo.fn_splitFunction(@yearval, ',') f on yearfield like f.<functionReturnColumn> + '%'WHERE ART.ARTTYPE = 'W' AND ART.WARSTATUS = 'W' Be One with the OptimizerTG |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-05 : 15:39:23
|
Is fn_splitFunction a built-in function because when I add it in, it says it's not recognized? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-02-05 : 15:44:41
|
No, it is not built in (check my original post). Sorry there are a lot to choose from. You can start looking through these highlighted sections:CSV / Splitting delimited listsBe One with the OptimizerTG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 01:13:52
|
quote: Originally posted by fralo Thank so much Vis. Does your select cast handle LIKE? My incoming values will be like ECSO12ARR, ECSO13ARR but I need to search for anything that begins with that: 'ECSO12ARR%' OR 'ECSO13ARR%'.Also, "Youryearfieldhere" refers to the database filed, correct?
it can. just use contains as per illustration belowdeclare @t table(id int,field varchar(20))insert @tvalues(1,'test'),(2,'giu'),(3,'pieceofmeal'),(4,'csvtext'),(5,'master'),(7,'pattern')declare @t1 table(id int,list varchar(1000))insert @t1values (1,'this,is,test,for,csv,search,piece'),(2,'finding,string,pattern,within,given,csv,value')SELECT t.id,field,listFROM @t tINNER JOIN (SELECT id,list,CAST('<Node><Row>' + replace(list,',','</Row><Row>') + '</Row></Node>' as xml) AS XMLList FROm @t1) t1ON XMLList.exist('/Node/Row/text()[contains(sql:column("field"),.)]')=1output--------------------------------------------------------------id field list--------------------------------------------------------------1 test this,is,test,for,csv,search,piece3 pieceofmeal this,is,test,for,csv,search,piece4 csvtext this,is,test,for,csv,search,piece4 csvtext finding,string,pattern,within,given,csv,value7 pattern finding,string,pattern,within,given,csv,value ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-06 : 08:47:58
|
I've almost got it guys. If you can just bear with me one more moment...I found this split function.ALTER FUNCTION [dbo].[Split]( @RowData nvarchar(500), @SplitOn nvarchar(5)) RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100)) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (data) Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (data) Select Data = ltrim(rtrim(@RowData)) ReturnENDAnd am using this in my select:FROM MNI..MNI WITH (NOLOCK) INNER JOIN MNI..MNI_ADD WITH (NOLOCK) ON MNI_ADD.ECSOID = MNI.ECSOID INNER JOIN ARREST..ARREST WITH (NOLOCK) ON ARREST.ECSOID = MNI.ECSOID INNER JOIN ARREST..ARR_AFSS WITH (NOLOCK) ON ARREST.ARRESTNO = ARR_AFSS.ARRESTNO inner join dbo.Split(@yearval, ',') f on ARREST.arrestno like f.<functionReturnColumn> + '%'I don't know what to place in <functionReturnColumn>. I've tried what I think is the return value: @RtnValue.But it doesn't like it.You don't know how much I've appreciated your help. |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-02-06 : 11:12:04
|
I was actually able to figure this one out.inner join dbo.Split(@yearval, ',') fon ARREST.arrestno like f.Data + '%'Thanks to you both for all your assistance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 11:12:06
|
it should be f.Data as thats field function returns inside table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|