October 21, 2010 at 2:57 pm
I was wondering does someone know how to implement a stored procedure that takes no input parameters. But I want this stored procedure to return the avearge of a numeric column of all the rows in a table. And I want to call this stored procedure from within a asp.net web application. I am primarily concerned about how to write the stored procedure. Thanks for any advice.
October 21, 2010 at 3:36 pm
This is the best I can do from my BlackBerry.
Read about stored procedures in Books Online, what you are looking for is the OUTPUT parameter in the parameter list.
Basically, your stored proc will have one parameter, and that would be an output parameter to return the value you are requesting.
October 21, 2010 at 3:48 pm
Unless you are updating tables, you could also do this as a user-defined funciton.
October 22, 2010 at 1:58 pm
The output param is an excellent approach.
CREATE PROCEDURE up_MyProc
--Create output param
@MyAverage money output
AS
SET @MyAverage = SELECT AVG(YourColumn) FROM (YourTable)
In your ASP code you need to create a command object and configure it to point to this SP and then add a parameter with parameterdirection set to output. Execute the SP in ASP and read the .Value of the param AFTER the execution.
Another approach is to just create the SP with the SELECT statement returning your result.
CREATE PROCEDURE up_MyProc
AS
SELECT AVG(YourColumn) AS MyAverage
FROM YourTable
Then you can actually use the command object's .ExecuteScalar method to read the result in ASP.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply