在SQL Server 2008中为“在子句中"传递参数

问题描述

大家好，
我正在使用以下程序
在我使用"in子句"表示接受inetger值的Attenancecodeid的地方，如何从vb.net传递我的"sql子句中的参数"的参数，我尝试了许多对我没用的解决方案，

更改过程MPEX_SP_ActivityException_Select
@startDate日期，@ endDate日期，@ AttCode nvarchar(max)
如
开始
在
上设置nocount 选择e.employeeId，e.Badge，e.LastName，e.FirstName，a.Date，ac.code
从员工e加入a.fkEmployee = e.employeeId的出勤率a
在ac.attendancecodesid = a.fkattendancecode上加入出勤代码ac
其中@startDate和@endDate之间的a.Date和(@AttCode)中的ac.code
结束
去

在此先感谢

Arasu

Hi All,
I am using a following procedure
where i use "in clause" for attendancecodeid which accepts inetger value,how i can pass parameters for my "sql procedure in clause" from vb.net,i tried many thing nothing worked for me looking for a solution,

alter procedure MPEX_SP_ActivityException_Select
@startDate date,@endDate date,@AttCode nvarchar(max)
as
begin
set nocount on
select e.employeeId,e.Badge,e.LastName,e.FirstName,a.Date,ac.code
from employee e join attendance a on a.fkEmployee=e.employeeId
join attendancecodes ac on ac.attendancecodesid=a.fkattendancecode
where a.Date between @startDate and @endDate and ac.code in (@AttCode )
end
go

Thanks in advance

Arasu

推荐答案

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab

在这种情况下，我刚刚用固定的字符串显示了它.
请注意，最后一个数字必须以分隔符结尾.

In this case, I have just shown it with a fixed string.
Do note that the last number must be terminated by a separator character.

这篇关于在SQL Server 2008中为“在子句中"传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！