5.3. Stored Procedures Stored procedures are routines that return tabular result sets, messages, and output parameters to the client and invoke DML and DDL statements. A CLR stored procedure is implemented as a public static method of a class in a .NET Framework assembly. The method is either void or returns an integer that is the return code from the stored procedure. A method declared void implicitly returns a stored procedure return code of 0. You identify a stored procedure by annotating the method that implements the stored procedure with the SqlProcedure attribute. The SqlProcedure attribute indicates that the method should be registered as a stored procedure. The SqlProcedure attribute has the following syntax: SqlProcedure [ ( procedure-attribute [ ,... ] ) ] procedure-attribute::= Name = "procedure name"
where:
Name Specifies the name of the stored procedure. Arguments to the stored procedure method can be any native SQL Server data type that has an equivalent in managed code. CLR stored procedures can return information to the client as messages, tabular result sets, and output parameters. Send messages and tabular result sets using one of the Send( ) methods of the SqlPipe object or using the ExecuteAndSend( ) method of the SqlPipe object. The SqlPipe object is described in Chapter 4. Output parameters are arguments that are passed in the same way as other output arguments (i.e., using the out keyword in C#). The following example creates, registers, and executes a stored procedure that returns a tabular result set of all employees that work a specified shift from the HumanResources.Shift table in AdventureWorks. The stored procedure takes the shift ID as its only argument. Follow these steps: Using the Visual Studio 2005 IDE, create a new SQL Server project named StoredProcedure. Create a stored procedure item in the project. Name the item EmployeesInShift.cs. Replace the EmployeesInShiftCode.cs code with the following: using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.Data.SqlClient; public partial class StoredProcedures { [SqlProcedure] public static void EmployeesInShift(int shiftID) { using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open( ); SqlCommand cmd = new SqlCommand( "SELECT e.* FROM HumanResources.Employee e " + "JOIN HumanResources.EmployeeDepartmentHistory h " + "ON e.EmployeeID = h.EmployeeID " + "WHERE h.ShiftID = " + shiftID, conn); SqlContext.Pipe.ExecuteAndSend(cmd); } } };
The EmployeesInShift( ) method implements the stored procedure and is annotated with the StoredProcedure attribute. The tabular result set is returned to the client using the ExecuteAndSend( ) method of the SqlPipe object that executes a command and sends the tabular result set directly to the client. The method takes a single parameter that is a SqlCommand object associated with the context connection. Alternatively, you can send a tabular result set to the client using either the Send(SqlDataReader) or Send(SqlDataRecord) method of the SqlPipe object. The following line of code replaces the ExecuteAndSend( ) method used in this example with the Send(SqlDataReader) method: SqlContext.Pipe.Send(cmd.ExecuteReader( ));
The Send( ) methods lets you manipulate the data before you send it to the client but is slightly slower because of additional overhead. Build the solution. Register the assembly and create the stored procedure by executing the following T-SQL statement in SQL Server Management Studio: USE AdventureWorks GO CREATE ASSEMBLY EmployeeInShift FROM 'C:\PSS2005\StoredProcedure\StoredProcedure\bin\Debug\StoredProcedure.dll' GO CREATE PROCEDURE EmployeeByShiftSP @shiftID int AS EXTERNAL NAME EmployeeInShift.StoredProcedures.EmployeesInShift
Execute the stored procedure: EXEC EmployeeByShiftSP @shiftID=1
The resulting set is all of the employees for the specified shift ID. Partial results are shown in Figure 5-3.
Figure 5-3. Results for stored procedure example that returns a tabular result set 
The following CLR stored procedure example returns an output parameter, a message, and a return code: Add the following method to the StoredProcedure class created in the preceding example: [SqlProcedure] public static int EmployeeByShift2SP(out int outputVal) { outputVal = 10; SqlContext.Pipe.Send("Test message."); return 5; }
The limit for the return string is 8000 characters. Extra characters are truncated. Build the solution. Update the assembly registration in SQL Server and create the new stored procedure by executing this T-SQL statement: ALTER ASSEMBLY EmployeeInShift FROM 'C:\PSS2005\StoredProcedure\StoredProcedure\bin\Debug\StoredProcedure.dll' GO CREATE PROCEDURE EmployeeByShift2SP @outputVal int OUT AS EXTERNAL NAME EmployeeInShift.StoredProcedures.EmployeeByShift2SP
Execute the stored procedure: DECLARE @returnCode int DECLARE @outputVal int EXEC @returnCode = EmployeeByShift2SP @outputVal OUTPUT PRINT 'Return code = ' + CAST(@returnCode AS CHAR(5)) PRINT 'Output value @outputVal = ' + CAST(@outputVal AS CHAR(5))
Results are shown in Figure 5-4.
Figure 5-4. Results for stored procedure example that returns an output parameter, a message, and a return code 
The following CLR stored procedure example returns a result set containing a single row of data created dynamically by the stored procedure, as shown next. Add the following method to the StoredProcedure class created in the preceding example: [SqlProcedure] public static void DynamicSingleRow( ) { SqlMetaData [] md = new SqlMetaData[2]; md[0] = new SqlMetaData("intCol", SqlDbType.Int); md[1] = new SqlMetaData("stringCol", SqlDbType.NVarChar, 50); SqlDataRecord row = new SqlDataRecord(md); row.SetSqlInt32(0, 1); row.SetSqlString(1, "Record 1"); SqlContext.Pipe.Send(row); }
The method uses the SqlMetaData class to define the schema of the result set row. The row is created as an instance of the SqlDataRecord class. The row values are filled using the Set( ) methods of SqlDataRecord. The Set( ) methods take two argumentsan ordinal specifying the column number and the value. Finally, an overload of the SqlPipe.Send( ) method is used to return the instance of the SqlDataRecord class as the result set row. You cannot extend this example to return a result set containing multiple rows since a new result set is returned each time the Send( ) method is called. The next example shows how to return a dynamically created result set containing multiple rows. Build the solution. Update the assembly registration in SQL Server and create the new stored procedure by executing this T-SQL statement: ALTER ASSEMBLY EmployeeInShift FROM 'C:\PSS2005\StoredProcedure\StoredProcedure\bin\Debug\StoredProcedure.dll' GO CREATE PROCEDURE DynamicSingleRow AS EXTERNAL NAME EmployeeInShift.StoredProcedures.DynamicSingleRow
Execute the stored procedure: EXEC DynamicSingleRow
Results are shown in Figure 5-5. The following CLR stored procedure example returns a result set containing two rows of data created dynamically by the stored procedure: Add the following method to the StoredProcedure class created in the preceding example: [SqlProcedure] public static void DynamicMultiRow( ) {
Figure 5-5. Results for stored procedure example that returns a dynamically created single-row result set  SqlMetaData[] md = new SqlMetaData[2]; md[0] = new SqlMetaData("intCol", SqlDbType.Int); md[1] = new SqlMetaData("stringCol", SqlDbType.NVarChar, 50); SqlDataRecord row = new SqlDataRecord(md); SqlContext.Pipe.SendResultsStart(row); // create and send the first record row.SetSqlInt32(0, 1); row.SetSqlString(1, "Record 1"); SqlContext.Pipe.SendResultsRow(row); // create and send the second record row.SetSqlInt32(0, 2); row.SetSqlString(1, "Record 2"); SqlContext.Pipe.SendResultsRow(row); SqlContext.Pipe.SendResultsEnd( ); }
The SqlResultsStart( ), SqlResultsSend( ), and SqlResultsEnd( ) methods of the SqlPipe class are used to send dynamically created result sets containing multiple rows. The SqlResultsStart( ) method takes a SqlMetaData array argument from which the schema of the result set is inferred. The SqlResultsRow( ) method is called for each row to return in the result set. It can be called any time after SqlResultsStart( ) is called and before SqlResultsEnd( ) is called marking the end of the result set. Build the solution. Update the assembly registration in SQL Server and create the new stored procedure by executing this T-SQL statement: ALTER ASSEMBLY EmployeeInShift FROM 'C:\PSS2005\StoredProcedure\StoredProcedure\bin\Debug\StoredProcedure.dll' GO CREATE PROCEDURE DynamicMultiRow AS EXTERNAL NAME EmployeeInShift.StoredProcedures.DynamicMultiRow
Execute the stored procedure: EXEC DynamicMultiRow
Results are shown in Figure 5-6. Figure 5-6. Results for stored procedure example that returns a dynamically created multi-row result set 
|
