Call a stored procedure from a SELECT statement? 1

[TheInsider]

Hi,
I am using SQL Server 7 and I have run into a little dilemma. I have been working in Access for a few years and have come accustom to writing user-defined public functions in a VBA module when I need to do so. These functions can then be placed seamlessly into Access queries to do row-by-row calculations.

I am able to create the functions that I need in SQL Server using stored procedures, but apparently you can’t call a stored procedure from within a SELECT statement.

For example:

-------------------------------------------------------------------
CREATE PROCEDURE spSumOrdersByID
	@CustomerID int
AS
	DECLARE @OrderSum money	

SELECT @OrderSum = SUM(OrderPrice)
FROM Orders
WHERE CustomerID = @CustomerID

RETURN @OrderSum
--------------------------------------------------------------------

Then in Query Analyzer I would have something like this:

SELECT CustomerID, CustomerName, EXEC spSumOrdersByID CustomerID AS OrderTotal FROM Customers

The above is a fictitious [and poor] example of what I am trying to do. I realize that A) the above example does not work, and B) there are much simpler ways to obtain that information in the latter case. My real situation is a little bit more complicated and the SELECT statement will be dynamically generated.

So far the only work-around that I can see is a complex series of sub-queries which wouldn’t be as fast or efficient as a stored procedure.

The problem is the fact that I need a row-by-row calculation in the SELECT statement, which is based on data in other tables.

Stored procedures seem perfect for creating user-defined functions. I am sure that other SQL Server developers do this all of the time, but how? PS. I have read Ion's FAQ in this forum, which simply states: no you can't.
Thanks

 

[fluteplr]

No you can't. At least not in SQL7 you can do what you want in SQL2000 with user functions.

 

[cmmrfrds]

You could create a stored procedure with a cursor where you step through the resultset row by row, then call another stored procedure(s) that contain your row data that you need along with each record in the initial resultset.

 

[TheInsider]

Thanks fluteplr and cmmrfrds. Unfortunately I can't use SQL Server 2000 right now. A lot of money has already been spent on setting up these projects in SQL Server 7. I have come to the conclusion that I will need to do as cmmrfrds suggested. The problem is that the SQL statements must be dynamically generated, and to my knowledge you cannot build a dynamic SQL statement in a stored procedure, as you would do by concatenating strings in Visual Basic. I need to return a particular value which is deturmined by a Top N query (the N litterally needs to be dynamic, as it is based on a parameter). Then from the Top N query, I do a little more processing and return the value. This is supposed to be a row-by-row calculation, so the query that calls this "function" would sort by the return value. It's definately going to take me a while to solve this one.

 

[SpeedOLight]

Actually, you can build dynamic SQL statements within a stored proc. Build the SQL statement in an nvarchar variable. Then use the Execute sp_executeSQL statement to execute it.

Hope this helps...

 

[TheInsider]

Thanks SpeedOLight! I didn't know that was possible.