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'