Stored proc as the SELECT statement of a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored proc as the SELECT statement of a cursor?

Author

Topic

LukeBallantine
Starting Member

4 Posts

Posted - 2005-04-19 : 06:47:34

Does anyone know if it is possible to have a stored procedure as the SELECT statement of a CURSOR?

Ideally I would like to have something like:

DECLARE curTest CURSOR FOR
EXEC usp_GetUsers

This doesn't seem to work however.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-19 : 07:29:52

for inline call, use function instead of sp

--------------------
keeping it simple...

LukeBallantine
Starting Member

4 Posts

Posted - 2005-04-19 : 08:52:19

quote:
Originally posted by jen

for inline call, use function instead of sp

--------------------
keeping it simple...

LukeBallantine
Starting Member

4 Posts

Posted - 2005-04-19 : 08:53:19

quote:
Originally posted by jen

for inline call, use function instead of sp

--------------------
keeping it simple...

Sorry for previous post - forgot this part:

I don't follow. Are you saying I should use a function instead of an sp? If you can write functions within T-SQL in the C#/VB sense then I have some reading to do.

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-04-20 : 02:38:32

UDF functions can create a table for use in a query
----------------------------------------------
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
End
---------------------------------------------------------------------------------
Then go something like this:
Select * from LargeOrderShppers( @SomeValue)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-20 : 04:50:08

yap, search 'create function' in BOL

quote:
Originally posted by LukeBallantine

quote:
Originally posted by jen

for inline call, use function instead of sp

--------------------
keeping it simple...

Sorry for previous post - forgot this part:

I don't follow. Are you saying I should use a function instead of an sp? If you can write functions within T-SQL in the C#/VB sense then I have some reading to do.

--------------------
keeping it simple...

LukeBallantine
Starting Member

4 Posts

Posted - 2005-04-22 : 04:12:03

Aha - have just upgraded from SQL 7 to 2000. I don't think UDFs were available in SQL 7.

Great solution - thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources