如何为该过程编写选择语句 [英] how to write select statement for this procedure

问题描述

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

当用户输入"c777"时，iam会正确获取第三条记录为"stringval c777".
而且我也想要iRnumber和Drawingnumber .........如何编写select语句......
请帮助我..........

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..........

推荐答案

用以下内容替换您的最后一个select语句:

Replace your last select statement with the following :

declare @var varchar(100)
select @var = stringval from @output where(substring(stringval,1,10)=@subcompname)

select sno, @var as stringval, iRnumber, Drawingnumber from iRnumber where subcompname like '%' + @var + '%'

这篇关于如何为该过程编写选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！