In a final article on scripting stored
procedures, we'll look at usp's employing local variables and temporary tables.
Local variables are useful to hold information common to multiple scripts
within a usp. An example of local variables is shown below:
CREATE procedure usp_get_scores_stats @@rangeA dec output,
@@rangeB dec output,@@rangeC dec output
AS
Declare @50_60 int
Declare @60_70 int
Declare @70_100 int
Declare @all int
SELECT @50_60 = count(username) FROM exam_results
WHERE score > 50 AND score <= 60
SELECT @60_70 = count(username) FROM exam_results
WHERE score > 60 AND score <= 70
SELECT @70_100 = count(username) FROM exam_results
WHERE score > 70 AND score <= 100
SELECT @all = count(username) FROM exam_results
Set @@rangeA = @50_60 / @all * 100
Set @@rangeB = @60_70 / @all * 100
Set @@rangeC = @70_100 / @all * 100
Using intermediate variables, this returns the 3 output parameters, each
holding the percentage population of it's score range.
The following usp, a table is queried which holds total sales for each
day, for products in various sales sectors. The usp employs a temporary
table to hold all the the individual sales figures for multiple product
with the latest date. Then the temporary table's sales are aggregated up
to produce the total current sales for the sector:
CREATE procedure usp_get_todays_sales @sector varchar(15),
@@todays_sales dec output
AS
SELECT sales, max(sales_date) into #tmp_todays_sales
FROM daily_sales
WHERE sector = @sector
GROUP BY sales
SELECT @@todays_sales = sum(sales)
FROM #tmp_todays_sales |
