SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to pass id's dynamically in this query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/17/2013 :  23:46:22  Show Profile  Reply with Quote
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
52249 Posts

Posted - 01/17/2013 :  23:54:07  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/18/2013 :  00:41:27  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 01/18/2013 :  00:43:20  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/18/2013 :  01:21:57  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/18/2013 :  01:23:37  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 01/18/2013 :  01:40:35  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/18/2013 :  01:46:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000