在SQL Server 2008中为“在子句中"传递参数 [英] Passing parameters for “In Clause” in 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

推荐答案

如果我正确理解了您想传递的参数列表，然后在IN子句中使用then的功能?

如果是这样的话，我会阅读表参数

将列表/数组传递给SQL Server [ MSDN表值参数 [

If I get you correctly you want to pass in a list of parameters and then use then in a IN clause?

If so then I would read up on Table Parameters

Passing a list/array to SQL Server[^]
MSDN Table-Valued Parameters[^]

In my opinion I dont think you''d be able to do this robustly with using the IN clause in SQL Server.

这很复杂，因为SQL没有概念数组，并且无法从无法转换为单个int的字符串参数使用.
但是，您可以传递一个字符串参数，并将其切"入一个临时表中:

The is quite complicated, because SQL does not have a concept of arrays, and will not work from a string parameter that it can''t convert to a single int.
However, you can pass a string parameter, and "chop" it into a temporary table:

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中为“在子句中"传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！