Using SELECT INTO within a Scalar Function

[myschelle848691]

Hi Guys,

So i've just been told that SELECT INTO cannot be used within a scalar function. Can anyone please tell me why?

Secondly, can I use a cursor on a view? and all within a function?

Thirdly, if not, can I define a cursor on various fields from four different tables using INNER JOIN i.e.:

DECLARE cursor_name CURSOR local SCROLL
FOR SELECT p.field_one, q.field_two, r.field_one
FROM Product p INNER JOIN Query q
ON p.ProductID = q.ProductID
INNER JOIN Racing r
ON q.RoadID = r.RoadID
WHERE p.ProductID = @PurchaseOrderID

DECLARE VARIABLES HERE
OPEN CURSOR

I'm using SQL Server 2005 and working with the adventureworks sample database. Any help would be much appreciated.

Cheers,

Michelle

[azimmer]

Hi Guys,

So i've just been told that SELECT INTO cannot be used within a scalar function. Can anyone please tell me why?

Secondly, can I use a cursor on a view? and all within a function?

Thirdly, if not, can I define a cursor on various fields from four different tables using INNER JOIN i.e.:

DECLARE cursor_name CURSOR local SCROLL
FOR SELECT p.field_one, q.field_two, r.field_one
FROM Product p INNER JOIN Query q
ON p.ProductID = q.ProductID
INNER JOIN Racing r
ON q.RoadID = r.RoadID
WHERE p.ProductID = @PurchaseOrderID

DECLARE VARIABLES HERE
OPEN CURSOR

I'm using SQL Server 2005 and working with the adventureworks sample database. Any help would be much appreciated.

Cheers,

Michelle

I don't know what exact limits have been removed in SQL Server 2005 but in 2000 some of the limits imposed on functions is sometimes nasty. The workaround I use is either use a PROCEDURE instead of a function, or if it has to be a function I make up a procedure to do the job and create a wrapper function... I know of no better way.

To my knowledge there's no limit on using cursors on views - outside or within a function. Nearly any SELECT will do for a cursor so joining tables is perfectly OK.