troubleshooting Question

Stored Procedure to select results IN ( )

Avatar of Aleks
AleksFlag for United States of America asked on
Web DevelopmentMicrosoft SQL Server 2008SQL
39 Comments2 Solutions124 ViewsLast Modified:
I have a stored procedure, it will get one Integer parameter and the other will be a comma delimited parameter with numbers in them, such as :  1,34,35,3

I need it to return all results in which the id is any of those numbers. This is what I have:

USE [BlueDot];
GO

/****** Object:  StoredProcedure [dbo].[report_basic_employer_01_results]    Script Date: 12/29/2015 14:13:02 ******/
SET ANSI_NULLS OFF;
GO

SET QUOTED_IDENTIFIER OFF;
GO




ALTER PROCEDURE [dbo].[report_basic_employer_01_results] @Firmid INT,
    @Empids INT
AS
    SELECT  c.Id ,
            c.CaseId ,
            c.EmpId ,
            c.AlienId ,
            c.FirmAddressId ,
            c.ExpDate ,
            d.FirstNm ,
            d.MiddleNm ,
            d.LastNm ,
            d.Email ,
            d.NiStatus ,
            d.ExpiresOn ,
            c.FirmId ,
            c.MainCase ,
            d.NIVMaxStatus ,
            b.MaidenNm ,
            e.Processcatalog ,
            c.Archived ,
            f.Filedon ,
            f.Approvedon ,
            f.Validtodate
    FROM    Cases c
            LEFT JOIN Users AS d ON c.AlienId = d.UserId
            INNER JOIN Processcatalog AS e ON c.Process = e.ProcesscatalogID
            INNER JOIN Users AS b ON c.EmpId = b.UserId
            LEFT JOIN Activities_Misc AS f ON f.act_misc_id = c.Defaultrecpt
    WHERE   c.FirmId = @Firmid
            AND c.EmpId IN (@Empids)
            AND c.Archived = 0
    ORDER BY d.LastNm ,
            c.CaseId;

1. Question:  Is the query syntax correct for the :              AND c.EmpId IN (@Empid)
2. Is the variable @Empids  also an INT ? or should I have it a a varchar ?

Help is appreciated. I am using MS SQL 2008
ASKER CERTIFIED SOLUTION
Brian Crowe
Database Engineer
Join our community to see this answer!
Unlock 2 Answers and 39 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 2 Answers and 39 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