| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
208 Posts |
 Posted - 01/17/2013 : 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's
here i am using stuff statement
declare @ving int
set @ving = 17
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) )
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=2
Suggest me .....
P.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 01/17/2013 : 23:54:07
|
two methods
1. string comparison method
declare @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) )
FROM
Mst_Meta_Values mt
WHERE ',' + + ',' LIKE '%,' + CAST(mt.Meta_Value_ID AS varchar(5)) + ',%'
--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=2
2. string parsing method
declare @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) )
FROM
Mst_Meta_Values mt
INNER JOIN dbo.ParseValues(@vingids,',') f
ON f.Val = mt.Meta_Value_ID
--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=2
ParseValues can be found in below link
http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 01/17/2013 23:54:28 |
 |
|
|
mohan123
Posting Yak Master
India
208 Posts |
Posted - 01/18/2013 : 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 function
P.V.P.MOhan |
|
|
|
mohan123
Posting Yak Master
India
208 Posts |
Posted - 01/18/2013 : 00:43:20
|
17,18,19...are ID's we need to get description realted of this ID's. So please look into that
P.V.P.MOhan |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 01/18/2013 : 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 function
P.V.P.MOhan
check the link posted. it has code for ParseValues. you need to copy and run that first to create ParseValues UDF
First statement will also work fine so long as you're passing values properly
show some sample data from tables and then explain how you're passing the values
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 01/18/2013 : 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 that
P.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 MVP
http://visakhm.blogspot.com/
|
|
|
|
mohan123
Posting Yak Master
India
208 Posts |
Posted - 01/18/2013 : 01:40:35
|
17 - good, 18 - verybad,19- bad
i will pass 17,18,19 and i need to get good,verybad,bad
i 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
India
48064 Posts |
Posted - 01/18/2013 : 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,bad
i 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 is
post some sample data and explain what you want
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
how to pass id's dynamically in this query
