ADO.NET 3.5 Cookbook, 2nd Edition

12.1. Creating a Stored Procedure

Problem

You need to create a CLR stored procedure.

Solution

The solution shows how to create four types of CLR stored procedures:

• A stored procedure that returns a result set

• A stored procedure that returns an output parameter, message, and return code

• A stored procedure that returns a dynamically created result set with a single row

• A stored procedure that returns a dynamically created result set containing multiple rows

The first solution is a CLR stored procedure that takes an input parameter @ShiftID and returns a result set of employees working that shift from the HumanResources.Employees table in AdventureWorks. Follow these steps:

1. Create a new SQL Server project in Visual Studio named ClrStoredProcedure.

2. Create a stored procedure item in the project; name it SpEmployeesInShift.cs.

3. The C# code in SpEmployeesInShift.cs in the project ClrStoredProcedure is shown in Example 12-1.

   Example 12-1. Stored procedure: SpEmployeesInShift.cs

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   
   public partial class StoredProcedures
   {
       [Microsoft.SqlServer.Server.SqlProcedure]
       public static void SpEmployeeInShift(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 ...