Last post Mar 29, 2016 11:03 AM by PatriceSc
Mar 28, 2016 03:00 PM|funluckykitty|LINK
I have a gridview with a template field with a checkbox. When a user checks more than one row from the gridview, I use a StringBuilder to loop thru the selected rows and create a comma separated list. that looks like this ('521', '552', '987'). Each value
represents an individual EmployeeID.
From there, I'm trying to pass that string into my stored procedure to return the 3 records for the three employees selected, but I'm having a hard time getting the "Type" right. In the database table, the EmployeeID is an Int, but by using StringBuilder,
I end up with a String of the three selected employees; so I end up with this error
Failed to convert parameter value from a String to a Int32.
Any suggestions on how to get my list of values to work with a storedprocedure that is expecting a list of integer values?
Here's a snippet from my codebehind where I'm trying to get the data.
cmd = new SqlCommand("proc_getEmployee", cnn);
cmd.CommandType = CommandType.StoredProcedure;
param = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
cmd.Parameters["@EmployeeID"].Value = Session["employeeString"]
Mar 28, 2016 03:43 PM|pawan rathour|LINK
Please pass employee id's as string instead of int.
Use as it is in stored procedure with the where clause.
Select * from EmployeesTable where employeid in ('521', '552', '987')
Mar 28, 2016 11:14 PM|markfitzme|LINK
I don't think that's going to work. You can't give it a comma separated list and expect it to be able to find the query. First, you're procedure needs an int because it's expecting a single user, not a bunch of them. You would need to modify it in order
to handle multiples. An easy way would be with XML. Instead of a comma separated list, which the database really won't know what to do with in this case, you pass structured XML.
When passing in XML, you can send it a string but the procedure would expect an XML datatype. Here is a sample of using XML to select a number of items in a table.
CREATE TABLE #Temp
Employeeid int not null
INSERT INTO #Temp
INSERT INTO #Temp VALUES (2)
INSERT INTO #Temp VALUES (3)
INSERT INTO #Temp VALUES (4)
DECLARE @EmployeeXML XML
= '<Employees><Employee EmployeeId="1" /><Employee EmployeeId="4" /></Employees>'
SELECT T.* from #Temp as T
@EmployeeXML.nodes('//Employees/Employee') as Employees(Employee)
on T.Employeeid = Employee.value('@EmployeeId','int')
DROP TABLE #Temp
Mar 29, 2016 02:43 AM|Chris Zhao|LINK
You cannot directly use the comma separated values in IN clause and no need of any create any functions for that. There are two approaches to do that.
1. Use Dynamic Sql to get records according to values in @recordId
2. Using XML format to split comma separated values to row and using IN
stored procedure selecting multiple record id
Mar 29, 2016 10:57 AM|paindaasp|LINK
This is a very simplified version, but you could do something like this:
ALTER PROCEDURE [dbo].[proc_getEmployee]
DECLARE @sqlIN nvarchar(500) = 'SELECT * FROM EmployeeTable WHERE EmployeeID IN ';
EXEC (@sqlIN + @EmployeeID)
Since, your EmployeeID is Int, you could pass in (521, 552, 987), without the quotes, if you want.
Mar 29, 2016 11:03 AM|PatriceSc|LINK
Or from SQL Server 2008 or later, another option is to pass a table-valued parameter:
The core issue is that a parameter doesn't work by replacing @MyParam with the string parameter and then running the statement. It is like a variable and can only be used where a literal is expected and replaces this literal value (so at best IN would search
for the "1,2,3" string rather than for any of those ids).