SQL Server Techniques for Creating a Web Reporting Page — Part 1

How CubeSP Works

First we have three session settings “SET NOCOUNT ON”, “SET ANSI_WARNINGS OFF”, & “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”. The first two settings are for aesthetics. “SET NOCOUNT ON” tells the stored procedure not to print how many rows were returned. SET ANSI_WARNINGS OFF tells the stored procedure not to print a warning that nulls were excluded from group by operations. “READ UNCOMMITTED” is important. It tells the stored procedure to perform dirty reads, preventing shared read locks. As we typically update the cube only once a day –- it doesn’t make sense to have shared locks. Shared locks would only make a web user wait, until the previous web user finishes performing his query.

Let’s go through the parameters:

@region. Possible values are: All, North, East, South, & West.

@empid. Possible values are: All, 1, 2, 12, 21… Note the @empid is varchar while the empid column is an INT.

@startdate, @enddate. Possible values are any datetime value.

@method is my attempt to add Object Oriented programming to Transact-SQL. For example, suppose we want not only to sum sales, but also to group sales. We can create another stored procedure. Or we can simply add another “method”. Notice each method code block has a “return” command. This tells the stored procedure to exit at that point.

LIKE Operator. The sales manager wants to be able to select “West” region or select ‘All’ regions. We can accomplish this by using dynamic web scripting, dynamic SQL, or by the LIKE operator. The LIKE operator is straightforward to use and is standard SQL. For example, we can code the WHERE statement as “where region like @region”. If @region=”west”, this is interpreted as “where region like ‘west’”. If @region=”%”, this is interpreted as “where region like ‘%’” which returns all rows. To use the LIKE operator, we need to reset @region to “%” when it is “ALL”. We do that by “select @region=case when @region=’all’ then ‘%’ else @region end”. Finally, note that the @empid variable is varchar, but the empid column is an integer. The statement “where empid like @empid” does an implicit conversion of empid into varchar then compares it to @empid. I have had cubes w/ over 10 million rows and rely on this implicit conversion w/ little adverse affect. If you’re worried about it, you can always convert all INT to varchar as you populate the cube.

Here are some results:

exec CubeSp @region =’west’ — returns 64

exec CubeSp @region =’all’ — returns 138

exec CubeSp @empid =’21’ — returns 4

exec CubeSp @empid =’all’ — returns 138

The sales manager can now search the cube by region and empid. Now he wants to group the results. That is he wants a breakdown by region and/or empid and filter the data at the same time. We alter our CubeSp and add a new method code block “groupSales”. In our web page, we implement the group functionality by input checkboxes.

<Our input checkboxes>

<input type=”Checkbox” value=”Region” name=”group”>Region

<input type=”Checkbox” value=”Empid” name=”group”>EmpID

</Our input checkboxes>

For example, he would check Region and EmpID, if he wants to group by both. Because the input checkboxes have the same name, there is only one “Group” variable and its value is “Region,EmpID”. If he only checked region, the value would be “Region”. Note that in html, if no box was checked, there would be no Group variable –- it is undefined. In that case, use web scripting to default the value to “All”. We then pass the value to the @Group parameter of our altered stored procedure.

<Our Reporting Stored Procedure>



@method varchar(50)=’groupSales’ –:sumSales, groupSales

,@region varchar(50)=’all’

,@startdate datetime=’1900/1/1′

,@enddate datetime=’3000/1/1′

,@empid varchar(50)=’all’

,@group varchar(5000)=null






select @region=case when @region=’all’ then ‘%’ else @region end

select @empid=case when @empid=’all’ then ‘%’ else @empid end

if @method=’sumSales’ begin

select sumSales=sum(N)

from cube

where [date] between @startdate and @enddate

and region like @region

and empid like @empid



if @method=’groupSales’ begin

select region,empid,sumSales=sum(N)




region=case when patindex(‘%region%’,@group)>0 then region else ” end

,empid=case when patindex(‘%empid%’,@group)>0 then empid else 0 end


from cube

where [date] between @startdate and @enddate

and region like @region

and empid like @empid

) as a

group by region,empid



</Our Reporting Stored Procedure>


Pages: 1 2 3


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |