I discovered how to call stored procedures with less than 5 lines of code. I
recently had to display a webpage that was based on a table that contained several
calculated totals. The reason I had this summary/reporting table is a lot of data
was calculated and allowed for almost anyway of displaying stat's about the data.
Here is a brief summary of what I did. I created a table in SQL 7 that held the
data.
Every 4 hours the table was refreshed with new data using a Stored
Procedure. Then I created another Stored procedure that was being called
from the ASP page. Without writing a book on what I did here is a 10,000 ft
level overview. Hope this demo shows another way of using Stored Procedures to
retrieve data a Data Source using ASP. Stored Procedures are no more than an SQL
statement retreving data. Why hold that Query inside an ASP page when you can use
the Database server to run your Query's
Step 1
Create a Reporting Table that will be populated with the totals. This is
the SQL Generated Script that will created the table. This table holds all the
calculated data that is based on Raw numbers that are inputted.
<p>CREATE TABLE [dbo].[test2] (
[NickName] [char] (50) NULL ,
[TotalPoints] [int] NULL ,
[Count_NickName] [char] (50) NULL ,
[AvgScore] [decimal](18, 1) NULL ,
[NumOfStrikes] [decimal](18, 0) NULL ,
[WinLossPercentage] [decimal](18, 2) NULL ,
[Wins] [int] NULL ,
[Loses] [int] NULL
) ON [PRIMARY]
GO
</p>
Step 1 1/2 Here is some data to load in the db.
| NickName | TotalPoints | Count_NickName | AvgScore | NumOfStrikes | Winning_Percentage | Wins | Loses | | NickName1 | 611 | 138 | 4.4 | 28 | 52.9 | 73 | 65 | | NickName2 | 445 | 94 | 4.7 | 28 | 57.45 | 54 | 40 | | NickName3 | 419 | 97 | 4.3 | 35 | 49.48 | 48 | 49 | | NickName4 | 383 | 83 | 4.6 | 24 | 51.81 | 43 | 40 | | NickName5 | 337 | 74 | 4.6 | 20 | 54.05 | 40 | 34 | | NickName6 | 329 | 79 | 4.2 | 14 | 55.7 | 44 | 35 | | NickName7 | 324 | 81 | 4 | 16 | 45.68 | 37 | 44 |
Step 2
Create Stored Procedure that will be called from an ASP page. Ok this is
just a normal select statement but I'm convinced that put code where it will run the
fastest. On the database server, you only have to call the stored Procedure and
return the data. Instead of parsing the query and then running the statement.
CREATE PROCEDURE [TestStoredProcedure] AS
SELECT test2.NickName, test2.TotalPoints, test2.Count_NickName, test2.AvgScore,
test2.NumOfStrikes, test2.WinLossPercentage, test2.Wins,test2.Loses
FROM test2
ORDER BY test2.NickName;
GO
Step 3
Create an ASP page. The normal stuff, connection string, recordset object
etc.. here is the code
<% @language="vbscript" %>
<%
dim conn
dim strconn
dim rs
dim strql
'Normal Connection String
strconn= "Driver={SQL
Server};SERVER=127.0.0.1;UID=LoginID;PWD=password;DATABASE=some_db"
set conn = server.createobject("adodb.connection")
conn.open strconn
'This is a local variable that holds the variable the execute the stored proc.
<font color="#FF0000">strsql = "Exec TestStoredProcedure"
set rs = server.createobject("adodb.recordset")
rs.open strsql, conn
%>
<html>
<head>
<title>Example</title>
</head>
<body>
<% rs.movefirst %>
<table BORDER="1" width="80%">
<tr></p>
<p><td colspan="8"><center><b><strong>This section writes out
the Names of the fields in the Recordse</strong>t</center></b></td></tr>
<tr>
<% For Each Field In RS.Fields %>
<th>
<%
response.write "<b>" & Field.name & "</b>"
%>
</th>
<% Next %>
</tr>
<tr></p>
<td colspan="8"><center>This section writes out
the DATA of the fields in the Recordset</strong></center></b></td></tr>
<% Do While Not RS.EOF %>
<tr>
<% For Each Field In RS.Fields %>
<td ALIGN="center">
<%
If IsNull(Field) Then
Response.Write ""
Else
Response.Write Field.Value
End If %>
</td>
<% Next
RS.MoveNext %>
<% Loop %>
</table>
</body>
</html>
<%
'Close all objects and set to nothing to keep resources free!
rs.close
conn.close
set rs = nothing
set conn = nothing
%></p>
|
Database Code 
/ 3 
OVERVIEW of Creatable Recordsets
