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 convert varchar to int in sql..		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sabareeswari
Starting Member

India
1 Posts
Posted - 12/14/2012 :  00:07:07  Show Profile  Reply with Quote
sql query
select AuditCategoryName from IS_AuditCategory where AuditCategoryId in(select CheckListCategoryIds from IS_AuditChecklistProjectScope where IS_AuditChecklistProjectScope.[PrimaryProjectScopeId ] =2)

Error---
Conversion failed when converting the varchar value '46,48,43,45,44' to data type int.

sabari

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 12/14/2012 :  00:39:59  Show Profile  Reply with Quote
Try this...

select AuditCategoryName 
from IS_AuditCategory t1
JOIN (select CheckListCategoryIds 
		from IS_AuditChecklistProjectScope 
		where IS_AuditChecklistProjectScope.[PrimaryProjectScopeId]=2
	  )t2
ON ','+CheckListCategoryIds+',' LIKE '%,'+CAST(AuditCategoryId as varchar(30))+',%'


Let me know the result

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 12/14/2012 :  02:32:37  Show Profile  Reply with Quote
or use a string parsing UDF to get values onto a table and then join to that


DECLARE @CategoryIDs varchar(8000)

select @CategoryIDs=CheckListCategoryIds 
		from IS_AuditChecklistProjectScope 
		where IS_AuditChecklistProjectScope.[PrimaryProjectScopeId]=2

select AuditCategoryName 
from IS_AuditCategory t1
JOIN dbo.ParseValues(@CategoryIDs,',') f
ON f.Val = t1.AuditCategoryId 


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
  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.03 seconds. Powered By: Snitz Forums 2000