I have a SQL Server 2005 stored procedure that I am trying to call from an ASP.NET web application. I am passing parameters via the web app. I've been doing this for a while, but today I ran across a situation where I wanted to pass a variable for an IN clause to a stored procedure, and SQL Server isn't liking it. Here's the proc:
CREATE PROCEDURE spGetPeople
SET NOCOUNT ON;
SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS PersonName,
Title, JobDescription, EMailAddress1
WHERE Classification IN (@Classifications) AND
Department IN (@Departments)
ORDER BY LastName, FirstName, EMailAddress1
And here's how I'm calling it from the web app:
<asp:SqlDataSource ID="dsGetPeople" runat="server" ConnectionString="<%$ ConnectionStrings:Default %>"
<asp:Parameter Name="Departments" DefaultValue="4" />
<asp:Parameter Name="Classifications" DefaultValue="6,7" />
So, you see that I want the stored proc to search for all people who have a Department of 4, and a Classification of 6 or 7. But, SQL Server is throwing this error:
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '6,7' to data type tinyint.
Classification and Department are both TinyInt types that are foreign keys to other tables. They need to remain as integers.
Any ideas on how to do this correctly?