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>'
