Take a Peek at Stored Procedures

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

DataStream

LANGUAGES: VB .NET

ASP.NET VERSIONS: 1.0 | .1

 

Take a Peek at Stored Procedures

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

 

By Wayne S. Freeze

 

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

 

In this article, you'll learn about the database tables where the information about stored procedures is kept and how to access it using a set of stored procedures that isolate your application from the physical database structure. You also will learn how you can create and drop stored procedures in your database directly.

 

List Stored Procedures

The sysobjects table contains information about the database objects including tables, views, and stored procedures. When the xtype column 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 column stores a unique identifier used as a foreign key into other system tables. You can filter system stored procedures from user stored procedures by selecting only those rows where the category has a value of zero.

 

This Select statement retrieves a list of stored procedures 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 a system table named syscomments. Like the sysobjects table, the syscomments table holds information about a lot of different database objects. To find the source code for a particular stored procedure, you can use the id column from the sysobjects table as a foreign key into the syscomments table and extract the text column containing the stored procedure's source code:

 

Select @source=text

From syscomments c, sysobjects o

Where [email protected] and o.id = c.id

 

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

 

Know Your Parameters

In theory, you can extract all the information you need to know about the parameters for a stored procedure from the source code. SQL Server stores, however, a parsed form of this information in the syscolumns table. You can retrieve this information using the id column from the sysobjects table as a foreign key.

 

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

 

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

 

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 and [email protected]

Order By c.colid

 

View a Stored Procedure

Figure 1 shows a simple ASP.NET application that retrieves information about a stored procedure. Clicking on the View source code button executes the code shown in Figure 2. This routine begins by creating a SqlConnection object using the information supplied by the user, then it builds a SqlCommand object that calls the SysGetStoredProcedureSource stored procedure. The name of the stored procedure is collected from the currently selected item in the SPs dropdown list.

 


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

 

Sub Button2_Click(sender As Object, e As EventArgs)

 

Dim ds As New DataSet

 

Dim conn As New SqlConnection( _

   "Data source=" & DatabaseServer.Text & _

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

   ";Password=" & Password.Text & _

   ";Initial catalog=" & Database.Text)

 

Dim cmd As New SqlCommand("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. The SysGetStoredProcedureSource stored procedure retrieves the source code from the database and returns it as a parameter, thus avoiding the overhead associated with returning a row set.

 

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

 

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

 

Update a Stored Procedure

The Delete procedure button executes the Drop Procedure command using the name of the currently selected stored procedure from the SPs dropdown list (see Figure 3). The code that executes the command is contained in a Try statement to trap any errors that might occur. It begins by assuming the command succeeds and storing a value in the Status label. Then it opens a connection to the database, runs the command, and closes it again. Any errors trigger the Catch clause, which copies the error message from the SqlException object.

 

Sub Button4_Click(sender As Object, e As EventArgs)

 

Dim conn As New SqlConnection( _

   "Data source=" & DatabaseServer.Text & _

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

   ";Password=" & Password.Text & _

   ";Initial catalog=" & Database.Text)

 

Dim cmd As new SqlCommand("Drop Procedure " & 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. This code drops a stored procedure by executing the Drop Procedure SQL command.

 

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

 

Sub Button5_Click(sender As Object, e As EventArgs)

 

Dim conn As New SqlConnection( _

   "Data source=" & DatabaseServer.Text & _

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

   ";Password=" & Password.Text & _

   ";Initial catalog=" & Database.Text)

 

Dim cmd As new SqlCommand(TextBox1.Text , conn)

 

Status.Text = "stored procedure created"

 

Try

   cmd.Connection.Open()

   cmd.ExecuteNonQuery()

   cmd.Connection.Close()

 

Catch ex as SqlException

   Status.Text = ex.Message

 

End Try

 

End Sub

Figure 4. This code creates a stored procedure by executing the Create Procedure statement contained in the textbox.

 

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

 

This article's sample code is available for download.

 

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

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish