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 |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-01-17 : 23:46:22
|
hello all,Here i am passing single id ang getting result but when the case of passing multiple ID's how i need to pass all the id's and with out declaring int value if i use IN condition i am getting result but there are lot of ID'shere i am using stuff statement declare @ving intset @ving = 17Select Convert(varchar,CPB.AgeMin)+' Years to '+Convert(varchar,CPB.AgeMax)+' Years', Convert(varchar,cpb.MinHeight)+' Cms to '+Convert(varchar,CPB.MaxHeight)+' Cms' , MtsMtrTon.MotherTongue,CPB.maritalstatusid,CPB.complexionid, STUFF(( SELECT ',' + CAST(mt.MetaValueDescription AS VARCHAR(20) ) FROM Mst_Meta_Values mt WHERE mt.Meta_Value_ID = @ving --in (17,18,19) FOR XML PATH('') ) , 1 , 1 , '') AS MetaValueDescription from Cust_Login CL left join Cust_PartnerPreferences_Basic CPB on CPB.Cust_ID=CL.Cust_ID left join Mst_MotherTongue MtsMtrTon on MtsMtrTon.MotherTongueID=CPB.MotherTongueID left join Mst_Meta_Values mt on mt.Meta_Value_ID=CPB.complexionid where CL.Cust_ID=2Suggest me .....P.V.P.MOhan |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-17 : 23:54:07
|
two methods1. string comparison methoddeclare @vingids varchar(1000)set @vingids = '17,18,20,...'Select Convert(varchar,CPB.AgeMin)+' Years to '+Convert(varchar,CPB.AgeMax)+' Years',Convert(varchar,cpb.MinHeight)+' Cms to '+Convert(varchar,CPB.MaxHeight)+' Cms' ,MtsMtrTon.MotherTongue,CPB.maritalstatusid,CPB.complexionid,STUFF(( SELECT ',' + CAST(mt.MetaValueDescription AS VARCHAR(20) ) FROMMst_Meta_Values mt WHERE ',' + + ',' LIKE '%,' + CAST(mt.Meta_Value_ID AS varchar(5)) + ',%'--in (17,18,19)FORXML PATH('') ) , 1 , 1 , '') AS MetaValueDescriptionfrom Cust_Login CL left join Cust_PartnerPreferences_Basic CPB on CPB.Cust_ID=CL.Cust_ID left join Mst_MotherTongue MtsMtrTon on MtsMtrTon.MotherTongueID=CPB.MotherTongueIDleft join Mst_Meta_Values mt on mt.Meta_Value_ID=CPB.complexionidwhere CL.Cust_ID=2 2. string parsing methoddeclare @vingids varchar(1000)set @vingids = '17,18,20,...'Select Convert(varchar,CPB.AgeMin)+' Years to '+Convert(varchar,CPB.AgeMax)+' Years',Convert(varchar,cpb.MinHeight)+' Cms to '+Convert(varchar,CPB.MaxHeight)+' Cms' ,MtsMtrTon.MotherTongue,CPB.maritalstatusid,CPB.complexionid,STUFF(( SELECT ',' + CAST(mt.MetaValueDescription AS VARCHAR(20) ) FROMMst_Meta_Values mt INNER JOIN dbo.ParseValues(@vingids,',') fON f.Val = mt.Meta_Value_ID--in (17,18,19)FORXML PATH('') ) , 1 , 1 , '') AS MetaValueDescriptionfrom Cust_Login CL left join Cust_PartnerPreferences_Basic CPB on CPB.Cust_ID=CL.Cust_ID left join Mst_MotherTongue MtsMtrTon on MtsMtrTon.MotherTongueID=CPB.MotherTongueIDleft join Mst_Meta_Values mt on mt.Meta_Value_ID=CPB.complexionidwhere CL.Cust_ID=2 ParseValues can be found in below linkhttp://visakhm.blogspot.in/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-01-18 : 00:41:27
|
hi visakah i executed both query none of them giving result it is getting NULLS and In 2nd query it showing error on dbo.ParseValues how to create ParseValues table or functionP.V.P.MOhan |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-01-18 : 00:43:20
|
17,18,19...are ID's we need to get description realted of this ID's. So please look into thatP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 01:21:57
|
quote: Originally posted by mohan123 hi visakah i executed both query none of them giving result it is getting NULLS and In 2nd query it showing error on dbo.ParseValues how to create ParseValues table or functionP.V.P.MOhan
check the link posted. it has code for ParseValues. you need to copy and run that first to create ParseValues UDFFirst statement will also work fine so long as you're passing values properlyshow some sample data from tables and then explain how you're passing the values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 01:23:37
|
quote: Originally posted by mohan123 17,18,19...are ID's we need to get description realted of this ID's. So please look into thatP.V.P.MOhan
as I see from your first post you've mt.Meta_Value_ID which you're using to filter against @ving parameter. thats same thing I'm using in my suggestion too. So i dont understand why you need to retrieve descriptions here------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-01-18 : 01:40:35
|
17 - good, 18 - verybad,19- bad i will pass 17,18,19 and i need to get good,verybad,badi am getting if i gave like where in (17,18,19) but id will be more in requirement so how to get dynamically....P.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 01:46:07
|
quote: Originally posted by mohan123 17 - good, 18 - verybad,19- bad i will pass 17,18,19 and i need to get good,verybad,badi am getting if i gave like where in (17,18,19) but id will be more in requirement so how to get dynamically....P.V.P.MOhan
i dont understand what you're asking for as I cant see how your data ispost some sample data and explain what you wanthttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|