|
Sometimes, like when you're about to write an article about an application that uses
a lot of stored procedures and you want an easy way to show them to your readers, you
may need to use a SQL query to display the contents of a stored procedure in an ASP
page. For just this occasion, I have written a couple of quick and easy utility
functions to do the work of displaying the stored procedures for me. The SQL function
used is called "sp_helptext", and takes as a single parameter the name of the stored
procedure that you wish to see the source of. Note, however, that this will not work on
encrypted SPROCs.
You can view the example to see the results
of the using the function. The source for the example is listed here:
/stevesmith/articles/examples/spview.asp<% OPTION EXPLICIT %>
<!-- #INCLUDE VIRTUAL="/libraryaspa/SPView.asp" -->
<object runat="server" progid="ADODB.Connection" id="objConn"></object>
<%
objConn.Open Application("aspdb_connectionstring")
%>
<html>
<head>
<title>ASPAlliance: Example (SPView)</title>
<link type="text/css" rel="stylesheet" href="http://www.aspalliance.com/stevesmith/include/ss.css" />
</head>
<body>
<%=SPView(objConn, "sp_ListTables")%>
</body>
</html>
|
As you can see, the example simply calls SPView, which is a function from an include file. The
source for the include file is listed here:
/libraryaspa/spview.asp<script language="vbscript" runat="server">
'GetSPText
'Expects an open connection object and a stored procedure name
'Returns the source of the stored procedure as a string with chr(10) for carriage returns
Function GetSPText(objConn, strSPName)
'On Error Resume Next
Dim sql
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "sp_helptext " & strSPName
Set rs = objConn.Execute(sql)
GetSPText = rs.GetString(adClipString,," "," ")
Set rs = Nothing
End Function
'SPView
'Expects an open connection object and a stored procedure name
'Returns an HTML formatted display of the stored procedure, wrapped in a table
Function SPView(objConn, strSPName)
SPView = "<table class=""code"" bgcolor=""#000000"" border=""0""><tr bgcolor=""#CCCCCC""><td>"
SPView = SPView & Replace(GetSPText(objConn, strSPName),chr(10),"<br/>")
SPView = SPView & "</td></tr></table>"
End Function
</script>
|
Very quick. Very easy. You'll be seeing this again soon in my next article on how to display all of
the tables or stored procedures in a database using ASP. Thanks, and hope this is helpful.
|