Using sp_helptext in classic asp

Question

When I want to get the definition of a stored procedure (in SQL Server) I use SQL Server Management Studio.

I sometimes run the `sp_helptext' to output the definition of a stored procedure.
Link: https://msdn.microsoft.com/en-us/library/ms176112.aspx

In a nutshell, I'd like to have a simple html popup window that would output any stored procedure's definition to text.

The asp code below is making a successful connection to my Database, but I'm not getting any output.

<% 
' .......
sql = "exec sp_helptext 'some_ProcName_Here'"
objRs.open sql, objConn
Text=objRs("Text")
response.write(Text) 
%>

I also attempted creating a new Stored Procedure that takes 1 parameter, which would execute the sp_helptext.

<% 
' .......
sql = "exec See_Proc_Definition @ProcName=some_ProcName_Here"
objRs.open sql, objConn
Text=objRs("Text")
response.write(Text) 
%>

Neither of these display anything, but I don't get any returned errors neither. Can anyone see what I'm doing wrong?

The issue here is `sp_helptext` produces one row per line so you need to `Do While Not objRs.EOF` and use `objRs.MoveNext` to move the cursor to each row. My guess is the definition you are trying to display contains a blank first line. — user692942, May 27 '15 at 12:27
If this is true, then the first line should displayed ? OP says no display ? — Whirl Mind, May 27 '15 at 12:31
It's a stored procedure, but it's getting called like it's a query — Mackan, May 27 '15 at 12:32
@Mackan True, but that is perfectly viable although personally I would use `ADODB.Command` to execute it. — user692942, May 27 '15 at 12:34
@Lankymart Ah, yes - because sp_helptext returns a recordset. And here I had an answer ready using _ADODB.command_ instead ;) — Mackan, May 27 '15 at 12:35
@Mackan I'm tempted to add a `ADODB.Command` equivalent myself. — user692942, May 27 '15 at 12:38

user692942 · Accepted Answer · 2015-06-01T14:23:36.927

The problem is you are only displaying the first line of the sp_HelpText output. SQL Server returns the output as a single column recordset containing a column called [Text].

This means you need to iterate through the rows to display the rest of the output.

Using your first example;

<% 
' .......
sql = "exec sp_helptext 'some_ProcName_Here'"
objRs.open sql, objConn
Do While Not objRs.EOF
  Text=objRs("Text")
  response.write(Text)
  objRS.MoveNext
Loop
%>

This isn't ideal but will work, from experience (especially with more complex stored procedures) I find something like this is better in the long run;

Dim sql, cmd, rs, data

Set cmd = Server.CreateObject("ADODB.Command")
sql = "sp_HelpText"
With cmd
  'Use your connection string instead of instantiating an ADODB.Connection object.
  .ActiveConnection = conn_string
  .CommandType = adCmdStoredProc
  .CommandText = sql
  .Parameters.Append(.CreateParameter("@objname", adVarWChar, adParamInput, 776))
  .Parameters.Append(.CreateParameter("@columnname", adVarWChar, adParamInput, 128))
  Set rs = .Execute(, Array("some_ProcName_Here"))
  If Not rs.EOF Then data = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

This method gives you a 2-Dimensional Array containing the row data in the data variable. You can then use standard Array techniques to manipulate the output.

Dim output, row, rows

If IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0 To rows
    output = output & "<br />" & data(0, row)
  Next
  Call Response.Write(output)
End If

Links

Using METADATA to Import DLL Constants - If your having trouble with the ADO constants (adCmdStoredProc etc.) this will fix it for you.

@WhirlMind As far as the OP can tell no output is displayed, that doesn't mean the code isn't working it just means the first line in the output is blank. — user692942, May 27 '15 at 12:35
Bonus point for the _Command_ ;) Also `Execute(, Array("some_ProcName_Here"))` is news to me. — Mackan, May 27 '15 at 13:01
@Mackan I talk a bit about this approach [here](http://stackoverflow.com/a/21698468/692942). The `Array()` approach was to me until a few years ago. — user692942, May 27 '15 at 13:03
Thanks for the link. I also checked _msdn_ and now I'm left to wonder why I haven't used any of these Execute parameters in my previous +10 years of classic asp coding ;) Some of those would've saved some time for sure (records affected etc.)! — Mackan, May 27 '15 at 13:10
@Mackan It was a kind of *"Eureka!"* moment when I first discovered it quickly followed by *"I could have used this for so many projects!"* realisation. — user692942, May 27 '15 at 13:19

Using sp_helptext in classic asp

1 Answers1