Search This Blog

Tuesday, October 26, 2010

Passing array of parameters to UDF/Stored Proc in SQL Server

With the beautiful XML support from SQL Server 2005 onwards, passing multiple values to Stored Procedure or User Defined Function has never been so easy. Please see the sample code


-- Pass all employeeId or other information together
-- in a sincle call of the stored procedure

DECLARE @x xml, @EMPID INT
SELECT @x =
  N'<Root>
      <Employee empId="1"/>
    </Root>'
SELECT E.EMPLOYEEID FROM TBLEMP E   
JOIN @x.nodes('Root/Employee') AS T(Item)
ON E.EMPLOYEEID = T.Item.value('@empId', 'int')

-- Writting the stored procedure

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

-- Test the procedure with the following
EXEC SP_EMPLOYEES N'<Root><Employee num="1"/><Employee num="2"/>
                             <Employee num="3"/><Employee num="4"/></Root>'

No comments:

Post a Comment