Another way to Pass list of parameters to SQL Server Stored Procedures

The following are the ways you could have solved

1) Passing inputs (all Ids at once) as a comma separated string
2) Since SQL Server 2005 is CLR compliant, you could have written a dll which could have performed the above task at a better performance rate

3) But third option is passing all the inputs as XML. And XML parsing may be complex but this approach will be far more efficient. Have a look at the code below

CREATE   PROCEDURE  SP_EMPLOYEES @ids xml   AS
    SELECT  E . EMPLOYEEID ,  E . MANAGERID ,  E . NAME
    FROM    TBLEMP E
    JOIN    @ids . nodes ( '/Root/Employee' )   AS  T ( Item )
      ON    E . EMPLOYEEID =  T . Item . value ( '@num' ,   'int' )
go
EXEC  SP_EMPLOYEES N '<Root><Employee num="1"/><Employee num="2"/>
                             <Employee num="3"/><Employee num="4"/></Root>'