get single record from comma separated values

[krunal1986]

Hi all,

i am passing comma separated values to my stored procedure. something like below:

@id=1,2,3,4
@firstname='a','b','c','d'

now if i write select statement then i want single data from @id and @firstname. something like below:

id firstname
1 a
2 b
3 c
4 d

thanks

[darkagn]

Check out this article for a description of several different solutions to your problem.

[M.Waqas Aslam]

do somthing like this

select * from table where id in (@id) and username(@firstname)

[ugi.nagesh]

Create the below user defined function fnsplit_string in sql server

CREATE FUNCTION [dbo].[FNSPLIT_STRING]        
(@STR1 VARCHAR(MAX),
@CTERMINATOR CHAR(1))            
RETURNS @TABLE TABLE(STRNAME VARCHAR(7000))            
AS            
BEGIN            

IF(@STR1 IS NOT NULL)        
BEGIN        
DECLARE @STR2 VARCHAR(MAX)            
DECLARE @INO INTEGER            
SELECT @INO=CHARINDEX(@CTERMINATOR,LTRIM(RTRIM(@STR1)),1)            

WHILE(@INO<>0)            
BEGIN            
   SELECT @STR2=SUBSTRING(LTRIM(RTRIM(@STR1)),1,@INO-1)            

   INSERT INTO @TABLE SELECT LTRIM(RTRIM(@STR2))            
   SELECT @STR2= SUBSTRING(LTRIM(RTRIM(@STR1)),@INO+1,LEN(LTRIM(RTRIM(@STR1))))           
   SELECT @INO=CHARINDEX(@CTERMINATOR,LTRIM(RTRIM(@STR2)),1)            
   SELECT @STR1=@STR2              
END            
   SELECT @STR2=SUBSTRING(LTRIM(RTRIM(@STR1)),1,LEN(LTRIM(RTRIM(@STR1))))            
   INSERT INTO @TABLE SELECT LTRIM(RTRIM(@STR2)) WHERE @STR2 <> ''          
   DELETE @TABLE WHERE LTRIM(RTRIM(STRNAME))=''        
END        


   RETURN             
END  

GO

Execute Syntax:

Select strname from dbo.fnsplit_string ('1,2,3,4',',')

OUTPUT

STRNAME
1
2
3
4

Based on this you can merge both the column id and first name

hope this will solve your problem...

Thanks