Browse DevX
Sign up for e-mail newsletters from DevX


Writing Managed Stored Procedures in SQL Server 2005 : Page 3

Are you tired of struggling with T-SQL to encapsulate the logic your applications need to get data into and out of SQL Server? Now you can bypass SQL altogether, and write logic that runs directly within SQL Server in your favorite .NET language.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Invoking Nested Stored Procedures
You aren't limited to running managed stored procedures one by one—you can chain or nest them as well. In this section you'll see how to invoke a CLR-based stored procedure from within another CLR-based stored procedure. For the purposes of this example, you'll create two stored procedures that, together, let you retrieve address type details based on the name of the address type. The capability is split into two stored procedures. The first procedure, GetAddressTypeIDByName, accepts the name of an address type and returns the corresponding address type ID. The second procedure, GetAddressTypeDetailsByName, accepts the name of an address type and returns the details of that address type. It does this in two steps. In the first step, it invokes the GetAddressTypeIDByName procedure to convert the name of the address type into an address type ID. It then uses ID to retrieve the corresponding address type record.

To start, create a new class named AddressTypeID and add a method named GetAddressTypeIDByName as shown in the following code.

Imports System.Data.Sql Imports System.Data.SqlServer Public class AddressTypeID Public Shared Function GetAddressTypeIDByName( _ ByVal name _As String) As Integer Dim sp as SqlPipe = _SqlContext.GetPipe() Dim cmd as SqlCommand = _SqlContext.GetCommand() cmd.CommandText = "SELECT " & _" AddressTypeID " & _ "FROM " &_" Person.AddressType WHERE " & _ "Name='" + name + "'" Dim addressTypeID As Integer = _CType( _ cmd.ExecuteScalar(),Integer) Return addressTypeID End Function End Class

You can see that the code is very similar to the previous stored procedure. It simply takes in the name of an address type and returns the corresponding address type identifier. Next, add a GetAddressDetailsByName method to the existing AddressType class as shown below.

Public Shared Sub GetAddressTypeDetailsByName _ (ByVal name as String) Dim sp as SqlPipe = SqlContext.GetPipe() Dim cmd as SqlCommand = SqlContext.GetCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "GetAddressTypeIDByName" Dim paramName as New SqlParameter("@name", _ SqlDbType.NVarChar,50) paramName.Direction = ParameterDirection.Input paramName.Value = name cmd.Parameters.Add(paramName) Dim paramID as New SqlParameter( _ "@ID",SqlDbType.Int) paramID.Direction = ParameterDirection.ReturnValue cmd.Parameters.Add(paramID) cmd.ExecuteNonQuery() ' Get the returned value from the stored procedure Dim id as Integer = CType( _ cmd.Parameters(1).Value, _Integer) ' Use this id as an input value for the ' execution of ' next stored procedure cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT * FROM " & _ "Person.AddressType " & _ "Where AddressTypeID=" + id.ToString() Dim rdr as SqlDataReader = cmd.ExecuteReader() sp.Send(rdr) End Sub

The preceding code consists of two parts. The first part invokes the GetAddressTypeIDByName stored procedure to convert the address type name into an address type ID. The second part retrieves the details of the address type using that ID. Finally the method returns the results back to the calling application using the Send method. Download the sample code to get this test client application and other code shown in this article.

Thanks for your registration, follow us on our social networks to keep up-to-date