Search This Blog

Thursday, July 28, 2011

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

No comments:

Post a Comment