Alison's SQL Server 2000 Tips
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.