Home > Articles > Data > SQL Server

Alison's SQL Server 2000 Tips

  • Print
  • + Share This
  • 💬 Discuss
Alison Balter provides tips on returning multiple records sets and executing the "first and last after trigger" in SQL Server 2000.

Returning multiple record sets

Many people don't know that SQL Server stored procedures can return multiple record sets. The following is an example:

CREATE PROCEDURE 
procCustomerGet
@CustomerID VarChar(5)
AS
     SELECT * FROM Customers
	   WHERE CustomerID = 
           @CustomerID
     SELECT * FROM Orders
           WHERE CustomerID =
           @CustomerID

This procedure is called by a Visual Basic or Web application as follows:

Dim rst As Recordset
 Set rst = New ADODB.Recordset
 rst.Open _
        Source:=ÓEXEC Ò & _
            ÒprocCustomerGetWithOrders ÔÒ & _
            Me.txtCustomerID.Text & ÒÔÒ, _
        ActiveConnection:= _
            CONNSTRING
    MsgBox rst(ÒCompanyNameÓ)
    Set rst = rst.NextRecordset
MsgBox rst(ÒOrderIDÓ)

Notice that after the stored procedure is called, the code displays the value of the CompanyName field of the selected customer. The example uses the NextRecordset method to move to the next result set. The contents of the OrderID field are retrieved from the OrderID field.

SQL Server 2000 allows you to designate the "first and last after trigger" to execute.

After triggers execute after constraints are checked. After triggers existed in versions of SQL Server prior to SQL 2000. What is new with after triggers in SQL 2000 is the introduction of keyword AFTER (used in place of FOR). This makes SQL 2000 compliant with the ANSI SQL standard. If constraints are violated, after triggers are not fired.

New to SQL 2000, you can set the first and last after triggers to execute. Here are some examples:

Sp_settriggerorder @triggername = 'FirstTrigger', @order='first'
Sp_settriggerorder @triggername = 'LastTrigger', @order='last'
Sp_settriggerorder @triggername = 'AnyTrigger', @order='none'

It is important to note that when you alter a trigger, you must reset the order.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus