PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
Stored Procedure Creation(3)
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
 

Top of Page

Legal Notice

Ken Howe 2011