表tb
sn errcode
101 1,5,3
102 5,3
103 13,16
104 3,4
105 8
106 2,1,3
107 4,3,7
108 13,16,6
109 3,4,6
110 8,3,2,1,5想把这条语句写成存储过程,就是查找errcode字段中含有 某一个 的记录,比如3
select * from tb where errcode LIKE '%3' or errcode LIKE '3%' or errcode LIKE '%,3%'
---------------------------------------------------------
CREATE PROCEDURE errcodeQuery
@errcode char(5)
AS
?????该怎么写????GO
----------------------------------------------------------
sn errcode
101 1,5,3
102 5,3
103 13,16
104 3,4
105 8
106 2,1,3
107 4,3,7
108 13,16,6
109 3,4,6
110 8,3,2,1,5想把这条语句写成存储过程,就是查找errcode字段中含有 某一个 的记录,比如3
select * from tb where errcode LIKE '%3' or errcode LIKE '3%' or errcode LIKE '%,3%'
---------------------------------------------------------
CREATE PROCEDURE errcodeQuery
@errcode char(5)
AS
?????该怎么写????GO
----------------------------------------------------------
解决方案 »
- 作业经常执行失败,问题一直找不到!!!请各位帮忙看看
- 对单表插入一百万行数据,使用和不使用executeBatch()有什么区别?
- 求一个SQL语句,急!
- 求大神看看这两条相似的SQL语句 为什么执行效率相差这么大。。。。求大神帮助,小弟实在理解不了
- where code in('123300180000','123300180001',.......)里面参数太多时提示"Message empty ", in里面参数最多支持多少?
- 如何将表A中信息写到表B中
- 超时问题:timeout expired!
- 求前1/3记录中某个字段的平均数,出错了
- 高分求救:怎样打开*.vip文件?给100分!!!
- SQL中单价分摊问题
- sql server query 问题
- 用UNION ALL连接统计结果需要时,必须写两遍WHERE吗?
@errcode varchar(5)
AS
select * from tb
where errcode like '%' + @errcode + '%'
*
from
tb
where
errcode
like
'%' + @errcode + '%'
@errcode char(5)
AS
select * from tb where charindex(','+@errcode+',',','+errcode+',')>0
直接SQL语句就行了SELECT * FROM TB WHERE CHARINDEX(','+@errcode+',',','+errcode+',')>0
CREATE PROCEDURE errcodeQuery
@errcode varchar(5)
AS
select * from tb
where errcode like '%' + @errcode + '%'
go
SELECT sn ,errcode
from 表tb
WHERE errcode like '%@errcode%'
CREATE PROCEDURE errcodeQuery
@errcode char(5)
AS select * from tb where charindex(','+@errcode+',',','+errcode+',')>0
select * from tb where errcode LIKE '%3%'
但筛选出来的结果有
103 13,16
不符合要求
--try:CREATE PROCEDURE errcodeQuery
@errcode varchar(5)
AS
select * from tb
where charindex(','+@errcode+',',','+errcode+',')>0
用charindex方法可以做,如下
select * from tb where charindex(','+'3'+',',','+errcode+',')>0 101 1,5,3
102 5,3
104 3,4
106 2,1,3
107 4,3,7
109 3,4,6
110 8,3,2,1,5
但是作为存储过程,就显示不出来了,不知是何原因
exec errcodeQuery 3
后一条记录也显示不出来
是何原因??
如:
set rowcount 5
没有啊存储过程里就一条select语句
CREATE PROCEDURE errcodeQuery
@errcode varchar(5)
AS
select * from tb
where charindex(','+@errcode+',',','+errcode+',')>0
--我觉得是不是@errcode作为参数 会有什么问题?
@errcode varchar(5)
AS
select * from (select sn,','+errcode+',' errcode from tb ) a
where errcode like '%,' + @errcode + ',%'
@errcode char(5)
AS
exec ('select * from tb where charindex('','''+''''+@errcode+''''+''','','',''+errcode+'','')>0')
GO
CREATE PROCEDURE errcodeQuery
@errcode varchar(50)
AS
select * from tb
where charindex(','+@errcode+',',','+errcode+',')>0不是有人在你写好了吗
运行
exec errcodeQuery 3
一条记录也显示不出来 之前我不是说了么
CREATE PROCEDURE errcodeQuery
@errcode varchar(5)
AS
select * from (select sn,','+errcode+',' errcode from tb ) a
where errcode like '%,' + @errcode + ',%'
exec ('select * from tb where charindex('',''+'''+ @errcode + '''+'','','',''+errcode+'','')>0')
exec ('select * from tb where charindex('','''+ '+'''+ @errcode + '''+' +''','','',''+errcode+'','')>0')
两个试过了 都不行,可能还是格式不行,高手再看看
--方法一
CREATE PROCEDURE my_proc1 @errcode varchar(10)
AS
select * from tb where charindex(',' + @errcode + ',' , ',' + sn + ',') > 0
GO
--方法二
CREATE PROCEDURE my_proc2 @errcode varchar(10)
AS
select * from tb where ',' + sn + ',' like '%,' + @errcode + ',%'
GO
DECLARE @errcode VARCHAR(10)
SET @errcode='3'PRINT ('select * from tb where charindex('',''+'''+ @errcode + '''+'','','',''+errcode+'','')>0') /*
select * from tb where charindex(','+'3'+',',','+errcode+',')>0
为什么不行了????