How do I create a user defined function that returns a cursor from my query

How do I create a 'user defined function' that returns a 'cursor' back to the user from the following query:

SELECT 
   P.AddressId,
   P.DepartmentId
FROM
   Person P
INNER JOIN
   ACCOUNT A
ON p.UserId = A.UserId

I'm looking for a solution that is written in TSQL and is compatible with SS2008. Any column labeled Id is type int. We would like to return two columns to the caller instead of j开发者_JS百科ust a scalar. We intend to call the function, and write additional queries off the result of this function.

Edit: According to StarShip3000, the solution is to return a table. I believe the tsql for this:

CREATE FUNCTION nameOfFunction()
RETURNS @result TABLE
(
   AddressId INT,
   DeviceId INT
)
AS
BEGIN
INSERT INTO @result (AddressId, DeviceId)

SELECT 
   P.AddressId,
   P.DepartmentId
FROM
   Person P
INNER JOIN
   ACCOUNT A
ON p.UserId = A.UserId

RETURN
END

---

By the sounds of what your trying to do you can make use of a temp table by inserting your data into it and referencing that table further down in the call stack of your session. So call proc1(insert rows into #MyTable)->proc2(read rows from #MyTable)->proc3 etc..

Most likely your coworker came from a Oracle shop where cursors are optimized and actually used all the time, though still not the ideal solution in most cases. In SQL Server cursors are normally considered the devil due to them not being optimized like they are for Oracle. In SQL Server 2008 and above you can also make use of table valued parameters to pass sets of data in and out of procedures. In reality you could always do this using session scoped temporary tables. Im on SQL Server 2012 and have yet to need to use table valued parameters because temp tables work so well, but I'm sure there are cases where the later may work the same or better.

It is always important though in any relational database to leverage set based logic whenever possible and avoid any construct or design that will require you to loop row by row. Granted there are edge cases where maybe that is the only way, but make sure it's the only way.

---

My question is why do you need a function that returns the addressId and departmentId? Seems too trivial to merit creating a function for it. I can't see how a function would be useful in this situation.

Do you guys mean views instead of functions???

So just create a view then.

CREATE VIEW dbo.vw_proprietryInfo
AS
BEGIN
   SELECT 
      P.AddressId,
      P.DepartmentId
   FROM
      Person P
      INNER JOIN ACCOUNT A
         ON p.UserId = A.UserId
END