Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 comma separated value
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kiranmurali
Yak Posting Veteran

India
55 Posts

Posted - 08/18/2010 :  14:12:15  Show Profile  Reply with Quote
i have a senario that i have to store the empid as comma seperated values and i have to call the parameter in the where condition of a query.

for example:
@empid=1,2,3,4(this is the parameter and i have to give the output based on these values)
select * from table1 where empid in (@empid)

pls give me some suggestions.

thanks in advance.
Kiranmurali

MSquared
Yak Posting Veteran

52 Posts

Posted - 08/18/2010 :  16:06:26  Show Profile  Reply with Quote
I have a split function that you can use to parse the string, then join it to the main table.



CREATE FUNCTION [dbo].[strSplit] (@arr AS NVARCHAR(MAX), @sep AS NCHAR(1)) 
RETURNS TABLE 
AS 
RETURN 
WITH  
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows 
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2) 
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4) 
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16) 
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256) 
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536) 
  ,Numbers AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)   
SELECT 
(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos, 
LTRIM(SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n)) AS element 
FROM Numbers 
WHERE n <= LEN(@arr) + 1 
AND SUBSTRING(@sep + @arr, n, 1) = @sep 
AND Numbers.n <= 1000 


declare @emp  table  
(EmpID int ,
EmpName varchar(20))

insert into @emp
select 1, 'Emp1' union all
select 2, 'Emp2' union all
select 3, 'Emp3' union all
select 4, 'Emp4' union all
select 5, 'Emp5' union all
select 6, 'Emp6'

declare @EmpId varchar(10)
set @empid='1,2,3,4'

select e.EmpID, e.EmpName 
from @emp e
	inner join [dbo].[strSplit] (@empid, ',') f
		on e.EmpID = f.element



For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

PavanKK
Starting Member

India
32 Posts

Posted - 08/19/2010 :  02:35:19  Show Profile  Reply with Quote
Instead of split function you can use following.

Reference : http://www.sqlservercentral.com/articles/Array/70702/

----------
declare @emp table
(EmpID int ,
EmpName varchar(20))

insert into @emp
select 1, 'Emp1' union all
select 2, 'Emp2' union all
select 3, 'Emp3' union all
select 4, 'Emp4' union all
select 5, 'Emp5' union all
select 6, 'Emp6'

declare @EmpId varchar(10)
set @empid='1,2,3,4'

select e.EmpID, e.EmpName
from @emp e
where CHARINDEX(','+CONVERT(VARCHAR,EmpID)+',',','+@EmpId+',',1)>0

-------------


KK
Go to Top of Page
  Previous Topic Topic Next 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.15 seconds. Powered By: Snitz Forums 2000