Here is stripped down T-Sql code that illustrates a “no dynamic sql” approach to the common requirement for SQL Server stored procedures to accept comma delimited ID values while maintaining strong types. SQL Server @TableVariables are more efficient than regular temp tables and enhance the performance and scalability of this technique. However @TableVariables are _NOT_ implemented as in-memory datastructures and _DO_ write to tempdb. For more information on this data type see the Microsoft Table Variable FAQ [microsoft.com].

This example assumes an Employee table with a varchar(50) EmployeeID field. @IDList is the dynamically created (by the calling app) comma delimited list of IDs to return. That comma delimited @IDlist is parsed and inserted as separate records into the @TableVar so they can be used for the IN clause without dynamic sql.

Among other things, avoiding dynamic sql allows an administrator to grant privileges for users to run the procedure even SELECT privileges have been removed.

CREATE PROCEDURE [dbo].[SelectEmployeesByID] 
@IDList varchar(2000) 
AS 
set arithignore on 
set arithabort off 
set ansi_warnings off 
set nocount on 
declare @IDListPosition int 
declare @ArrValue varchar(2000) 
--must declare correct data type for IDs 
declare @TableVar table ( EmployeeID varchar(50) NOT NULL ) 
set @IDList = COALESCE(@IDList ,'') 
IF @IDList <> '' 
BEGIN 
--add comma to end of list so user doesn't have to 
set @IDList = @IDList + ',' 
-- Loop through the comma delimited string list 
while patindex('%,%' , @IDList ) <> 0 
begin 
select @IDListPosition = patindex('%,%' , @IDList) 
select @ArrValue = left(@IDList, @IDListPosition - 1) 
-- Insert parsed ID into TableVar for "where in select"
INSERT INTO @TableVar (EmployeeID) VALUES (@ArrValue) 
-- Remove processed string 
select @IDList = stuff(@IDList, 1, @IDListPosition, '') 
END 
END
SELECT 
*
FROM 
[Employee] 
WHERE 
( 
[EmployeeID] IN (SELECT EmployeeID FROM @TableVar) 
) 
ORDER BY EmployeeID ASC;

or, for a possible performance boost, try adjusting the SELECT clause to a logically equivalent variation by dropping the IN operation in favor of doing an inner join on @tablevar like so :

SELECT 
* 
FROM 
[Employee] INNER JOIN @TableVar 
ON [Employee].EmployeeID = @TableVar.EmployeeID
ORDER BY [Employee].EmployeeID ASC;

to run the proc (for either formulation):

exec SelectEmployeesByID 'a48345,a34598,r9834,l234'