Avatar of Aleks
Aleks
Flag for United States of America

asked on 

Stored Procedure to select results IN ( )

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;

Open in new window


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
Web DevelopmentMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Aleks

8/22/2022 - Mon