Take a Peek at Stored Procedures

DataStream

LANGUAGES: VB .NET

ASP.NETVERSIONS: 1.0 | .1

Take a Peek at Stored Procedures

Learn about the database tables where information aboutstored procedures is kept ... and how to access it.

By Wayne S. Freeze

System tables are full of interesting information aboutthe objects stored inside the database. One thing you might find useful is thesource code to each stored procedure along with its name and type of eachparameter.

In this article, you'll learn about the database tableswhere the information about stored procedures is kept and how to access itusing a set of stored procedures that isolate your application from thephysical database structure. You also will learn how you can create and dropstored procedures in your database directly.

List Stored Procedures

The sysobjects table contains information about thedatabase objects including tables, views, and stored procedures. When the xtypecolumn has a value of P, the row contains information about a stored procedure.The name column holds the name of the stored procedure, and the id columnstores a unique identifier used as a foreign key into other system tables. Youcan filter system stored procedures from user stored procedures by selectingonly those rows where the category has a value of zero.

This Select statement retrieves a list of storedprocedures from the database:

Select name

From sysobjects

Where xtype="P" and category=0

Order By name

The source code to the stored procedure is stored in asystem table named syscomments. Like the sysobjects table, the syscommentstable holds information about a lot of different database objects. To find thesource code for a particular stored procedure, you can use the id column fromthe sysobjects table as a foreign key into the syscomments table and extractthe text column containing the stored procedure's source code:

Select @source=text

From syscomments c, sysobjects o

Where o.name=@Name and o.id = c.id

Note that this Select statement is designed to save thesource code as the variable @source, which means when this statement is used ina stored procedure, you can avoid all the overhead associated with returning arow set back to the calling program.

Know Your Parameters

In theory, you can extract all the information you need toknow about the parameters for a stored procedure from the source code. SQLServer stores, however, a parsed form of this information in the syscolumnstable. You can retrieve this information using the id column from thesysobjects table as a foreign key.

The syscolumns table contains the name, length, prec(precision), and scale of the type associated with the parameter. The xtypefield contains a reference to the systypes table, which contains the name ofthe type. The isnullable column contains a 1 if the parameter can hold a Nullvalue, while the isoutparam column contains a 1 if the parameter returns avalue from the stored procedure. The colid field is used to arrange theparameters in the same order as they were listed in the stored procedure'sdefinition.

You can use this SQL statement to retrieve informationabout the parameters for the stored procedure from the database. Note that ifthis Select statement doesn't return any rows, the stored procedure has noparameters:

Select c.Name, t.name 'Type', c.length, c.prec, c.scale, c.colid,c.isnullable, c.isoutparam

From syscolumns c, sysobjects o,systypes t

Where o.id = c.id and t.xtype = c.xtype ando.name=@StoredProcedure

Order By c.colid

View a Stored Procedure

Figure 1 shows a simple ASP.NET application that retrievesinformation about a stored procedure. Clicking on the View source code buttonexecutes the code shown in Figure 2. This routine begins by creating aSqlConnection object using the information supplied by the user, then it buildsa SqlCommand object that calls the SysGetStoredProcedureSource storedprocedure. The name of the stored procedure is collected from the currentlyselected item in the SPs dropdown list.

Figure 1. After logging into thedatabase server using the supplied information to build a connection, you canretrieve information about any stored procedure by choosing the storedprocedure from the dropdown menu and pressing the View source code or Viewparameters button.

Sub Button2_Click(sender As Object, eAs EventArgs)

Dim ds As New DataSet

Dim conn As New SqlConnection( _

   "Datasource=" & DatabaseServer.Text & _

   ";User id="& Userid.text & _

   ";Password="& Password.Text & _

   ";Initialcatalog=" & Database.Text)

Dim cmd As NewSqlCommand("SysGetStoredProcedureSource", conn)

Try

   Status.Text =""

   cmd.CommandType =CommandType.StoredProcedure

   cmd.Parameters.Add("@Name", SqlDbType.Varchar, 255).Value = _

      SPs.SelectedItem.Value

   cmd.Parameters.Add("@Source", SqlDbType.Varchar, 4000).Value =""

   cmd.Parameters("@Source").Direction= ParameterDirection.InputOutput

   cmd.Connection.Open()

   cmd.ExecuteNonQuery()

   cmd.Connection.Close()

   TextBox1.Text = cmd.Parameters("@Source").Value

Catch ex as SqlException

   Status.Text =ex.Message

End Try

End Sub

Figure 2. TheSysGetStoredProcedureSource stored procedure retrieves the source code from thedatabase and returns it as a parameter, thus avoiding the overhead associatedwith returning a row set.

Note that because the stored procedure returns the sourcecode through an output parameter named @Source in the stored procedure, youmust specify the appropriate value for the Direction property explicitly.

The parameters associated with the stored procedure can bedisplayed in a datagrid by calling the SysGetStoredProcedureParameters storedprocedure and binding the returned data to the datagrid.

Update a Stored Procedure

The Delete procedure button executes the Drop Procedurecommand using the name of the currently selected stored procedure from the SPsdropdown list (see Figure 3). The code that executes the command is containedin a Try statement to trap any errors that might occur. It begins by assumingthe command succeeds and storing a value in the Status label. Then it opens aconnection to the database, runs the command, and closes it again. Any errorstrigger the Catch clause, which copies the error message from the SqlExceptionobject.

Sub Button4_Click(sender As Object, eAs EventArgs)

Dim conn As New SqlConnection( _

   "Datasource=" & DatabaseServer.Text & _

   ";User id="& Userid.text & _

   ";Password="& Password.Text & _

   ";Initialcatalog=" & Database.Text)

Dim cmd As new SqlCommand("DropProcedure " & SPs.SelectedItem.Value , conn)

Try

   Status.Text ="stored procedure deleted"

   cmd.Connection.Open()

   cmd.ExecuteNonQuery()

   cmd.Connection.Close()

Catch ex as SqlException

   Status.Text =ex.Message

End Try

End Sub

Figure 3. Thiscode drops a stored procedure by executing the Drop Procedure SQL command.

The Add procedure button takes the text of the storedprocedure and builds a SqlCommand object from it. Then it uses logic similar tothe Drop procedure button (see Figure 4) to create the new stored procedure.

Sub Button5_Click(sender As Object, eAs EventArgs)

Dim conn As New SqlConnection( _

   "Datasource=" & DatabaseServer.Text & _

   ";User id="& Userid.text & _

   ";Password="& Password.Text & _

   ";Initialcatalog=" & Database.Text)

Dim cmd As newSqlCommand(TextBox1.Text , conn)

Status.Text = "stored procedurecreated"

Try

   cmd.Connection.Open()

   cmd.ExecuteNonQuery()

   cmd.Connection.Close()

Catch ex as SqlException

   Status.Text =ex.Message

End Try

End Sub

Figure 4. Thiscode creates a stored procedure by executing the Create Procedure statementcontained in the textbox.

Before including this code in a live program, you shouldbe aware that any code entered into the textbox is executed as a SQL statement.This means anyone with the proper authentication information could execute anyset of SQL statements they choose - not only the Create Table statement.Therefore, use this capability with care.

This article's sample code isavailable for download.

Wayne S. Freeze isa full-time computer book author with more than a dozen titles to his credit,including Windows Game Programming with VisualBasic and DirectX (Que) and UnlockingOLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25years of experience using all types of computers, from small, embeddedmicroprocessor control systems to large-scale IBM mainframes. Freeze has amaster's degree in management information systems as well as degrees incomputer science and engineering. You can visit his Web site at http://www.JustPC.comand send e-mail to mailto:[email protected].He loves reading e-mail from his readers, whose ideas, questions, and insightsoften provide inspiration for future books and articles.