troubleshooting Question

Stored Procedure with an IN clause

Avatar of CMES-IT
CMES-ITFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
6 Comments1 Solution355 ViewsLast Modified:
Hi,

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
      @Departments VARCHAR(20),
      @Classifications VARCHAR(20)
AS
BEGIN
      SET NOCOUNT ON;

      SELECT  FirstName + ' ' + MiddleName + ' ' + LastName AS PersonName,
                   Title, JobDescription, EMailAddress1
      FROM People
      WHERE Classification IN (@Classifications) AND
                 Department IN (@Departments)
      ORDER BY LastName, FirstName, EMailAddress1

END
GO

And here's how I'm calling it from the web app:

    <asp:SqlDataSource ID="dsGetPeople" runat="server" ConnectionString="<%$ ConnectionStrings:Default %>"
        SelectCommand="spGetPeople" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter Name="Departments" DefaultValue="4" />
            <asp:Parameter Name="Classifications" DefaultValue="6,7" />
        </SelectParameters>
    </asp:SqlDataSource>

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?
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros