query for getting subcategories [Resolved]

Posted by Bhanubysani under Sql Server on 4/22/2011 | Points: 10 | Views : 1265 | Status : [Member] | Replies : 4
ypinformation1:--
---------------------
ypid int,companyname nvarchar(100),othersubcategorys nvarchar(100)

subcategories:-=-
------------------
subcategoryid int
subcategory nvarchar(100)

when user selects subcategorys that ids will be stored in other subcategories column..

when user wants to edit the subcategories i want to display all the subcategories from subcategories table with checkboxes along with his selcted subcategorys will be checked..

iam getting a problem in getting subcategory names..plz check the query
declare @subcategorys nvarchar(100)
set @subcategorys =(select othersubcategories from ypinformation1 where ypid=11)
print @subcategorys
select SubCategory from SubCategories where convert(nvarchar,SubCategoryID) in (@subcategorys)

the subcategories are not dispalying error is convert varchar to int

Regards,
Bhanu Prakash Bysani



Responses

Posted by: T.saravanan on: 4/23/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi,

I think in your ypinformation1 table othersubcategorys column value have 1,2,3.
You directly pass that value into the query then only conversion error occurred because can't pass the varchar value into integer column.

Try this one...I am not sure is working fine because in my system i don't have SQL Server.So try it...

declare @subcategorys nvarchar(100)

declare @sQuery NVarchar(max)
set @subcategorys =(select othersubcategories from ypinformation1 where ypid=11)
set @sQuery ='select SubCategory from SubCategories where SubCategoryID in ('+@subcategorys+')'
exec sp_executesql @sQuery


else using split function to pass a value one by one into your query then add the value into temp table.
Refer this links its useful to you...
http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Thanks,
T.Saravanan

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 4/22/2011 [Member] Starter | Points: 25

Up
0
Down
Hi

Colud you please make it a bit more clear about table structure and how they are interrelated.
And what is the input and result for your query.

Thanks,
Debata

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bhanubysani on: 4/23/2011 [Member] Starter | Points: 25

Up
0
Down
split function is also working...which way is bettereither using splittinmg function or writing the query

Regards,
Bhanu Prakash Bysani

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 4/23/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Based on the condition we can choose query (or) function.But in your case using procedure is best just pass the input parameter then split the SubCategoryId's to insert the records into temp table then pass that table.

Any way you got an idea.Thanks.

Thanks,
T.Saravanan

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response