表myTable(id,toWhom),
其中toWhom字段是varchar,存放员工编号,多员工编号时用逗号分隔,
怎样写一存储过程myProcedure(@staffId int),使toWhom字段中包含有参数@staffId的记录被获取,不包含@staffId的记录不被获取.
谢谢,公司OA这个星期就要完工,急死我了!100分不够还可追加.
其中toWhom字段是varchar,存放员工编号,多员工编号时用逗号分隔,
怎样写一存储过程myProcedure(@staffId int),使toWhom字段中包含有参数@staffId的记录被获取,不包含@staffId的记录不被获取.
谢谢,公司OA这个星期就要完工,急死我了!100分不够还可追加.
@staffId int
as
declare @sql Nvarchar(4000)
set @sql = N'select * from myTable where toWhom like ''%' + Cast(@staffId as nvarchar(100)) + '%'''
exec sp_executesql @sql
set @strId = '%'+convert(varchar(20),@staffId)+'%'
select id from myTable where toWhom like @strId试试看
存储过程 无在乎 还是 SQL 语句create proc proc_Show @staffId varchar(1)
as select * from myTable where toWhom not like '+'''%'+@staffId + '%''
================
------------------------
哪如果这样的情况,你希望得到什么结果了???
As
Select * From myTable Where ',' + toWhom + ',' Like '%,' + Cast(@staffId As Varchar) + ',%'
GO
As
Select * From myTable Where CharIndex(',' + Cast(@staffId As Varchar) + ',', ',' + toWhom + ',') > 0
GO
Create Table myTable(id Int, toWhom Varchar(100))
--插入數據
Insert myTable Select 1, '1,2,3'
Union All Select 2, '3,10,11'
Union All Select 3, '111,112,113'
GO
--創建存儲過程
Create ProceDure myProcedure1(@staffId int)
As
Select * From myTable Where ',' + toWhom + ',' Like '%,' + Cast(@staffId As Varchar) + ',%'
GO
Create ProceDure myProcedure2(@staffId int)
As
Select * From myTable Where CharIndex(',' + Cast(@staffId As Varchar) + ',', ',' + toWhom + ',') > 0
GO
--測試
EXEC myProcedure1 1
EXEC myProcedure1 11EXEC myProcedure2 1
EXEC myProcedure2 11
GO
--刪除測試環境
Drop Table myTable
Drop ProceDure myProcedure1, myProcedure2
GO
--結果
/*
id toWhom
1 1,2,3id toWhom
2 3,10,11
*/
As
Select * From message Where CharIndex(Cast(@staffId As Varchar) + ',', toWhom + ',') > 0
go应该这样就行了,学习了paoluo(一天到晚游泳的鱼) ( ) 信誉:100