in statement not working with value pass by parameters

Question

i have a store prog where getting the department where being selected in the group of check box. when i pass one value it work but when i pass more than 2 dept it is not workiing

in store prog

CREATE PROCEDURE [dbo].[REPORT]
    @dept nvarchar(max)=null
AS
set @dept=left(@dept,len(@dept)-1)
select * from department where deptid in(@dept)

in my code behind

string dept = "";
foreach (ListItem item in CheckBoxList1.Items)
{
    if(item.Selected)
    {
        dept+="'"+item.Value.ToString()+"',";
    }
}
...
...
...
cmd.Parameters.AddWithValue("@dept", dept);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
    sda.Fill(dt);
    gvdata.datasource=dt;
    gvdata.bind();
}

Are you storing in @dept paramater the values separated with a comma? — sagi, Feb 04 '16 at 08:40
For that to work, you'll need to make your sql into a string and execute that. Otherwise, you cannot do that. — Allan S. Hansen, Feb 04 '16 at 08:41
Please don't mark code as a Javascript snippet unless it actually *is* Javascript... and please take a bit more time to format your code when posting. — Jon Skeet, Feb 04 '16 at 08:42
You can create a sql function which take in a comma-delimeted varchar, then parse the value into a table and return the table. — Dr. Stitch, Feb 04 '16 at 08:42
Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Dr. Stitch, Feb 04 '16 at 08:45
sorry for marking as javascript. im only beginner here in stackovverflow — Vic, Feb 04 '16 at 08:47

score 0 · Accepted Answer · answered Feb 04 '16 at 08:48

One way could be to make your query into "dynamic" sql (execute a sql string); this should work:

SET @dept=left(@dept,len(@dept)-1)
exec('select * from department where deptid in(' + @dept + ')');

Be aware of injection attacks when doing dynamic SQL though.

in statement not working with value pass by parameters

1 Answers1