query for getting subcategories [Resolved]

Posted by Bhanubysani under Sql Server on 4/22/2011 | Points: 10 | Views : 2273 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

Latest Posts