Passing parameters for "In Clause" in sql server 2008

Question

Possible Duplicate:
Parameterizing an SQL IN clause?

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

Duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) or http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable or 100s of others — gbn, Feb 27 '12 at 15:34

GarethD · Answer 1 · 2012-02-27T16:16:50.070

This could be a duplicate, but I can't find any answers to similar questions that suggest a table valued parameter for the stored procedure which could be a viable solution, and removes the need for dynamic SQL and the risk of SQL injection (not all hacks are associated with this risk). Table valued parameters should also offer better performance as the execution plan can be stored, rather than dynamic solution where the plan must be created on the fly.

CREATE TYPE dbo.AttCodeTableType AS TABLE (AttCode VARCHAR(MAX))
GO
CREATE PROCEDURE PEX_SP_ActivityException_Select  (@startDate DATE, @EndDate DATE, @AttCodes dbo.AttCodeTableType READONLY)
AS
BEGIN
    SELECT  e.EmployeeID, e.Badge, e.LastName, e.FirstName, a.Date, ac.Code
    FROM    Employee e
            INNER JOIN Attendance a
                ON a.FKEmployee = e.EmployeeID
            INNER JOIN AttendanceCodes ac
                ON ac.AttendanceCodesID = a.FKAttendanceCode
            INNER JOIN @AttCodes act
                ON act.AttCode = ac.Code
    WHERE   a.Date BETWEEN @StartDate AND @EndDate

END
GO

Then to execute the procedure you can use something like:

DECLARE @Table AS dbo.AttCodeTableType
INSERT @Table VALUES ('Code1'), ('Code2'), ('Code3')

EXEC PEX_SP_ActivityException_Select @Table

To create an SQL parameter from a datatable in vb.net you would use something like the following:

dim sqlParam as new SqlParameter("@Table", DataTable)
sqlParam.SqlDbType = SqlDbType.Structured
sqlParam.TypeName = "dbo.AttCodeTableType"

Read here for more info.

Finally, WORK ON YOUR ACCEPT RATE!!!

Passing parameters for "In Clause" in sql server 2008

1 Answers1