You can use some special techniques to write more robust and powerful stored procedures. In this hour you’ll learn
• How to work with temporary tables
• How to add error handling to your stored procedures
• How to use transaction processing within your stored procedures
Temporary tables can be useful in certain situations. When you need them they are invaluable. A temporary table can contain data from heterogeneous data sources (even a text file). Using temporary tables, you have access to methods that facilitate data access and retrieval. These methods enable you to sort, filter, index, and select a table’s rows.
The stored procedure found in Listing 17.1 first opens a connection. It then builds a string. The string first defines the structure of a temporary table. You know that the table is temporary because of the pound sign (#) preceding the table name. The string then contains an INSERT INTO
statement that inserts into the temporary table the results of selecting data from the Person.Contact table. Finally, the SQL statement selects the contents of the LastName and FirstName fields from the temporary table, ordering the results by LastName and FirstName.
After building the string, the code instantiates a command object, passing it the SQL statement along with a reference to the open connection. The ExecuteReader
method of the command object executes the SQL statement contained in the command object, returning the result to the SqlDataReader
object (rdr). Finally, the code sends the SqlDataReader
object, via a pipe, back to SQL Server. If you had selected data from the Person.Contact table, because no sort was applied, the data would have appeared in ContactID order. Because you selected the data from the temporary table, ordering the results by LastName combined with FirstName, the data appears in the specified order. You can find this code in the FirstSQLCLRClassVB
solution, located on the sample code disk. The code is contained in the class called FirstSQLCLRVB
.
LISTING 17.1. A Procedure That Uses a Temporary Table
Public Shared Function UsingTemporaryTables() as Integer
Dim conn As SqlConnection = New _
SqlConnection("context connection=true")
conn.Open()
Dim sql As String = "CREATE TABLE #TempContacts"
sql += " (ContactID int NOT NULL PRIMARY KEY, "
sql += " LastName varchar(50), "
sql += " FirstName varchar(50))"
sql += " INSERT INTO #TempContacts "
sql += " (ContactID, LastName, FirstName) "
sql += " SELECT Person.Contact.ContactID, "
sql += " Person.Contact.LastName, "
sql += " Person.Contact.FirstName From Person.Contact"
sql += " SELECT LastName, FirstName FROM #TempContacts " & _
"ORDER BY LastName, FirstName"
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim rdr As SqlDataReader = cmd.ExecuteReader()
SqlContext.Pipe.Send(rdr)
Return 0
End Function
You must place the function in a Visual Basic .NET class, and then build the solution to create a DLL. After you create the DLL you are ready for the next step. If you have not yet registered the FirstSQLCLRClassVB.dll
, the next step is to register the assembly in SQL Server Management Studio Express. This process was covered in Hour 16.
If you try to register an assembly, and it is already registered, you get an error. You must first delete the assembly from within Management Studio and then reregister it. Before you can delete an assembly, you must first delete all the stored procedures within it. In summary, when you update a DLL, you must go through the process of deleting the stored procedures, deleting the assembly, adding the assembly, and adding the stored procedures. You can easily use a SQL script to help you with this process. Note that if available, the assembly is listed under the Assemblies node (see Figure 17.1).
Figure 17.1. After the query is executed, the assembly is listed under the Assemblies node.
After you have ensured that the assembly is registered, you are ready to add any of its functions as stored procedures within SQL Server. The code found in Listing 17.2 accomplishes this task.
LISTING 17.2. Creating the Stored Procedure Called TemporaryTables
CREATE PROCEDURE TemporaryTables
AS
EXTERNAL NAME
FirstSQLCLRClassVB.[FirstSQLCLRClassVB.FirstSQLCLRVB].UsingTemporaryTables
You execute the stored procedure as you would any stored procedure (see Listing 17.3).
LISTING 17.3. Executing the Stored Procedure Called FirstListEmployees
use Adventureworks
Go
exec TemporaryTables
As shown previously in Listing 17.1, the stored procedure returns a SQLDataReader
with the last name and first name from the Contact table, ordering the result set by last name combined with first name. The results appear in Figure 17.2.
Figure 17.2. The stored procedure returns data sorted by last name and first name.
All stored procedures should contain error handling. In this way you control what happens when an error occurs. In Listing 17.4 the code first evaluates the groupName
parameter to determine whether it is Null
. If it is Null
, the code sends the text Group Name must be filled in
. back to SQL Server. Otherwise, the code instantiates a new connection. It opens the connection, then builds a string containing a SQL IF
statement. The statement evaluates the DepartmentName
parameter. If it is Null
, the statement builds a SELECT
statement, selecting the text No Department Name
and DepartmentID = 0
. Otherwise (Department Name Not Null
), the code builds an INSERT INTO
statement that inserts data into the HumanResources.Department table and a SELECT
statement that selects the message Success and the DepartmentID
of @@Identity
. @@Identity
is a system variable that contains the primary key value of the last inserted row. Finally, the code instantiates a command object, setting its parameters to the parameter values passed into the stored procedure. It uses the execute
method of the command
object to execute the stored procedure, sending the results back to SQL Server.
LISTING 17.4. A Stored Procedure Containing Error Handling
Public Shared Function HandlingRuntimeErrors(ByVal departmentName As String, _
ByVal groupName As String)
If groupName = "" Then
'Error handled in Visual Basic .NET
SqlContext.Pipe.Send("Group Name must be filled in.")
Else
Dim conn As SqlConnection =
New SqlConnection("context connection=true")
conn.Open()
'Error handled in T-SQL
Dim sql As String = "IF @DepartmentName Is Null "
sql += " BEGIN "
sql += " SELECT Message = 'No Department Name', "
sql += " DepartmentID = 0"
sql += " END "
sql += "ELSE "
sql += " BEGIN "
sql += " INSERT INTO "
sql += " HumanResources.Department(Name, GroupName) "
sql += " VALUES (@DepartmentName, @GroupName) "
sql += " SELECT Message = "
sql += " 'Success', DepartmentID = @@Identity "
sql += " END "
Dim cmd = New SqlCommand(sql, conn)
cmd.Parameters.Add(New SqlParameter _
("@DepartmentName", System.Data.SqlDbType.VarChar, 50))
cmd.Parameters(0).Value = departmentName
cmd.Parameters.Add(New SqlParameter _
("@GroupName", System.Data.SqlDbType.VarChar, 50))
cmd.Parameters(1).Value = groupName
Dim rdr As SqlDataReader = cmd.ExecuteReader()
SqlContext.Pipe.Send(rdr)
End If
Return 0
End Function
Because the assembly was created earlier, there is no need to take that step again. It is necessary that you define the stored procedure within SQL Server Management Studio Express. The code in Listing 17.5 accomplishes this task. Notice that the code creates a SQL Server procedure called HandlingErrors
based on the .NET procedure called HandlingRuntimeErrors
.
If you are building the samples yourself, rather than executing the sample code, you must unregister and reregister the component so that it can see the new procedure. The process of registering a component is covered in Hour 16. It is important to note that before you can unregister the component, you must first delete any stored procedures associated with it. You must then use the CREATE PROCEDURE
statement to re-establish the stored procedures within management studio.
LISTING 17.5. Creating the Stored Procedure Called SecondListEmployees
CREATE PROCEDURE HandlingErrors
(@DepartmentName NVarChar(20), @GroupName NVarChar(2))
AS
EXTERNAL NAME
FirstSQLCLRClassVB.[FirstSQLCLRClassVB.FirstSQLCLRVB].HandlingRuntimeErrors
You must pass the HandlingErrors
stored procedure two parameters when you execute it (see Listing 17.6). The first is the department name, and the second is the group name. The results of passing DVD Department and Null as parameters the first time, and then Null and Manufacturing as parameters the second time, appear in Figure 17.3 and Figure 17.4.
Figure 17.3. After parameters are passed to the stored procedure, the output appears with appropriate errors on the Results tab.
Figure 17.4. After parameters are passed to the stored procedure, the output appears with appropriate errors on the Messages tab.
LISTING 17.6. Executing the Stored Procedure Called HandlingErrors
Use AdventureWorks
Go
Exec HandlingErrors 'DVD Department', Null
Go
Exec HandlingErrors Null, 'Manufacturing'
Notice that whereas the Send
method sends the specified text to the Messages pane (see Figure 17.3), it sends the DataReader
object to the Results pane (see Figure 17.4).
Sometimes you may actually want to raise errors in your code. You may want to do this to simulate an error condition in your application, or to treat a user error like a system error, passing it through your standard error handler. Listing 17.7 provides an example. The procedure receives two parameters, departmentName
and groupName
. It instantiates and opens a connection. It then builds a SQL statement that inserts data into the HumanResources.Department table, using the parameter values passed to the procedure. Next it sets DepartmentID equal to the value of @@Identity
, the system variable containing the primary key value of the last inserted row. After the procedure builds the SQL statement, it declares and instantiates a command
object. It then adds parameters to the command
object, setting their values to the values passed in as parameters to the procedure. Next it uses the ExecuteAndSend
method of the SqlContext.Pipe
object, passing it the command
object, to return the results of the SQL statement to SQL Server. The difference between this procedure and the HandlingErrors
procedure lies in the Catch block. If an error occurs, the code uses the Send method of the SqlContext.Pipe
object to send a custom error message back to SQL Server.
LISTING 17.7. Sending a Custom Error Message to SQL Server
Public Shared Function RaisingErrors(ByVal departmentName As String, _
ByVal groupName As String) As Integer
Try
Dim conn As SqlConnection = New SqlConnection("context connection=true")
conn.Open()
Dim sql As String = "SET NOCOUNT ON INSERT INTO "
sql += "HumanResources.Department(Name, GroupName) VALUES "
sql += "(@DepartmentName, @GroupName) "
sql += "SELECT DepartmentID = @@Identity"
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
cmd.Parameters.Add(New SqlParameter("@DepartmentName", _
System.Data.SqlDbType.VarChar, 50))
cmd.Parameters(0).Value = departmentName
cmd.Parameters.Add(New SqlParameter("@GroupName", _
System.Data.SqlDbType.VarChar, 50))
cmd.Parameters(1).Value = groupName
SqlContext.Pipe.ExecuteAndSend(cmd)
Catch ex As Exception
'This error will get sent, rather than the other,
'to suppress this, comment out the next line.
SqlContext.Pipe.Send("An exception occurred during the " & _
"execution of RaisingErrors " + ex.ToString())
End Try
Return 0
End Function
Before you execute the stored procedure, you must define it within SQL Server Management Studio Express. The code in Listing 17.8 accomplishes this task. It creates a procedure called RaisingErrors
based on the .NET function called RaisingErrors
.
LISTING 17.8. Adding the Procedure Called RaisingErrors
to SQL Server
Use AdventureWorks
Go
CREATE PROCEDURE RaisingErrors(@departmentName NVarChar(50), @groupName
NVarChar(50))
AS
EXTERNAL NAME
FirstSQLCLRClassVB.[FirstSQLCLRClassVB.FirstSQLCLRVB].RaisingErrors
Now that you have defined the stored procedure to SQL Server, you are ready to execute it. Listing 17.9 provides an example. It passed Widget Department
and Null
as parameters to the stored procedure. Figure 17.5 shows the resulting error message. Notice that the highlighted text contains the text that the procedure sent when it raised the error.
Figure 17.5. The output appears with the custom error message.
LISTING 17.9. Executing the Procedure Called RaisingErrors
Use AdventureWorks
Go
Exec RaisingErrors 'DVD Department', Null
Sometimes you want to ensure that a series of statements execute completely or not at all. For example, if you were performing a bank transaction you would want to ensure that both the debit and credit occurred before committing the data. Transactions can help you solve this dilemma. Listing 17.10 provides an example.
The code begins by using a try...catch
block to establish error handling. It then declares and instantiates a connection object. It opens the connection and then begins to build a SQL string. The SQL string first declares three variables:
@DepartmentID
, @LocalError
, and @LocalRows
. The code then adds a BEGIN TRANSACTION
statement to the SQL string. From this point on, everything that follows is part of a transaction. Next the code adds an INSERT INTO
statement to the SQL statement, inserting the parameter values into the appropriate fields in the HumanResources.Department table. The code immediately tests to see whether an error occurred by testing the values of the system variables @@Error
and @@Rowcount
. @@Error
contains the number of the error that occurred, and @@Rowcount
contains the number of rows affected by the SQL statement that immediately precedes it.
After the error number and the number of rows affected have been captured, the code evaluates them to determine whether their values are indicative of problems. A non-zero error number means that an error occurred. Zero rows affected also means that an error occurred. If @LocalError
is non-zero or @LocalRows
is zero, a ROLLBACK TRANSACTION
occurs. The ROLLBACK TRANSACTION
obliterates any processing that occurred since the BEGIN TRANSACTION
statement. An END
statement then terminates processing. Otherwise, the code attempts to commit the transaction. It sets the DepartmentID
equal to the value of the system variable @@Identity
. It sets the value of Error equal to zero and the value of NumRows
equal to @LocalRows
.
After the SQL statement is built, it is time to execute it. The code declares and instantiates a command
object. It then adds two parameters to the command
object, one for departmentName
and one for groupName
. The code uses the ExecuteAndSend
method of the SQLContext
. Pipe object to execute the SQL statement, returning the results to SQL Server.
If an error occurs during processing, execution jumps to the Catch
block. The Catch
block sends the message Transaction Rolled Back
to SQL Server. If no error occurs, the COMMIT TRAN
statement commits the data to disk.
LISTING 17.10. A Stored Procedure That Receives Input Parameters
Public Shared Function UsingTransaction(ByVal departmentName As String, _
ByVal groupName As String) As Integer
Try
Dim conn As SqlConnection = _
New SqlConnection("context connection=true")
conn.Open()
Dim sql As String = "SET NOCOUNT ON "
sql += "DECLARE @DepartmentID int, @LocalError int, @LocalRows int "
sql += "BEGIN TRANSACTION "
sql += "INSERT INTO HumanResources.Department(Name, GroupName) "
sql += "VALUES (@DepartmentName, @GroupName) "
sql += "SELECT @LocalError = @@Error, @LocalRows = @@RowCount "
sql += "IF NOT @LocalError = 0 or @LocalRows = 0 "
sql += " BEGIN "
sql += " ROLLBACK TRANSACTION "
sql += " SELECT DepartmentID = Null, Error = @LocalError, "
sql += "NumRows = @LocalRows "
sql += " END "
sql += "ELSE "
sql += " BEGIN "
sql += " COMMIT TRAN "
sql += " SELECT @DepartmentID = @@Identity "
sql += " SELECT DepartmentID = @DepartmentID, Error = 0, "
sql += " NumRows = @LocalRows "
sql += " END "
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
cmd.Parameters.Add(New SqlParameter _
("@DepartmentName", System.Data.SqlDbType.VarChar, 50))
cmd.Parameters(0).Value = departmentName
cmd.Parameters.Add(New SqlParameter("@GroupName", _
System.Data.SqlDbType.VarChar, 50))
cmd.Parameters(1).Value = groupName
SqlContext.Pipe.ExecuteAndSend(cmd)
Catch ex As Exception
'This error will get sent, rather than the other,
'to suppress this, comment out the next line.
SqlContext.Pipe.Send("Transaction Rolled Back")
End Try
Return 0
End Function
As usual, you must first create the stored procedure within SQL Server Management Studio Express before you can execute it. Listing 17.11 provides an example. Note that when you declare the stored procedure, you declare the name of the parameter and its type.
LISTING 17.11. Creating a Stored Procedure That Contains Transactions
Use AdventureWorks
Go
CREATE PROCEDURE UsingTransaction(@departmentName NVarChar(50), @groupName
NVarChar(50))
AS
EXTERNAL NAME
FirstSQLCLRClassVB.[FirstSQLCLRClassVB.FirstSQLCLRVB].UsingTransaction
To execute the stored procedure, you must pass the designated parameter(s). Listing 17.12 provides an example. It first passes ‘Toy Creation
’ and ‘Manufacturing
’ to the stored procedure. This should successfully insert a row into the HumanResources.Department table (see Figure 17.6). If you execute the stored procedure a second time you will get an error and the code will roll back the transaction (see Figure 17.7). The table does not allow duplicate department names.
Figure 17.6. The stored procedure executes successfully.
Figure 17.7. The stored procedure executes with an error and the transaction is rolled back.
LISTING 17.12. Executing a Stored Procedure That Receives Input Parameters
Use AdventureWorks
Go
Exec UsingTransaction 'Toy Creation', 'Manufacturing'
Several techniques can help to make the stored procedures that you write more powerful and robust. These techniques include the use of temporary tables, error handling, and transaction processing in the stored procedures that you build. In this hour you learned these three invaluable techniques.
Q. Explain why you might want to use a temporary table.
A. Temporary tables enable you to perform operations on data as if that data were structured differently than it actually is. For example, using temporary tables, you can join two tables, have the result go to a temporary table, sort the results, and then send the results back to SQL Server. The temporary table is in memory only, so its structure does not have to be stored anywhere.
Q. Explain why error handling is useful.
A. Without error handling, you get the default error handling associated with Visual Basic .NET. This default error handling is probably not what you want. Using custom error handling you can control exactly what happens when an error occurs.
Q. Give an example of where transaction processing may be useful.
A. Transaction processing would be helpful, for example, for an airline ticket processing system. If processing is interrupted during the booking of a flight, you would not want a partial transaction to be entered into the system. Using transaction processing, you ensure that the entire transaction is completed or the processing that has occurred is rolled back.
1. What character do you use to indicate that you are working with a temporary table?
3. What is the name of the system variable that returns the number of rows affected by a SQL statement?
5. An error number of zero means that an error occurred (true/false).
6. What keywords do you use when things go wrong and you want to discard any changes that have been made?
2. It returns the key value of the most recently inserted row.
4. It returns the error number associated with the previous line of code.
Create, build, declare, and execute a stored procedure that inserts data into the Purchasing.Vendor table. Make sure that you write code to insert data into all the columns because all but one of the fields is required. Add error handling and transaction processing to the stored procedure. Make sure that one row is affected and that no error occurs before committing the transaction. Return an error message back to SQL Server when an error occurs.
18.220.64.128