alter proc getdata
@subcompname varchar(50)
as
begin
declare @liststr varchar(max)
select @liststr = coalesce(@liststr+',','')+subcompname from IRNUMBER
declare @subcomp varchar(max)=@liststr
declare @stringval varchar(100)
declare @output table([stringval] varchar(50))
while LEN(@subcomp)>0
begin
set @stringval = LEFT(@subcomp,ISNULL(nullif(charindex(',',@subcomp)-1,-1),len(@subcomp)))
set @subcomp= SUBSTRING(@subcomp,ISNULL(nullif(charindex(',',@subcomp),0),len(@subcomp))+1,len(@subcomp))
insert into @output([stringval]) values(@stringval)
end
select stringval from @output where(substring(stringval,1,10)=@subcompname)
iam having data like this
sno subcompname iRnumber Drawingnumber
1 c1,c2,c3 001 1
2 c200,c444,c555 002 2
3 c777,c878,c999 003 3
when user enters 'c777' iam getting third record correctly as "stringval c777".
and i also want iRnumber and Drawingnumber............how to write select statement......
please help me..........