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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to pass id's dynamically in this query

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

52326 Posts

Posted - 2013-01-17 : 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/

Go to Top of Page

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 function

P.V.P.MOhan
Go to Top of Page

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 that

P.V.P.MOhan
Go to Top of Page

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

Go to Top of Page

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

Go to Top of Page

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,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
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -