Database Reference
In-Depth Information
How It Works
This deployment process is a two-step process. First, you have to register an assembly (which you
created in C#) with your own name in SQL Server.
Create Assembly SQLCLR_StoredProcedure
from
'C:\VidyaVrat\C#2012 and SQL 2012\Chapter20\Code\Chapter20\bin\Debug\Chapter20.dll'
WITH PERMISSION_SET = UNSAFE
GO
This
PERMISSION_SET
property allows the user to execute assemblies with specific code access
permissions.
UNSAFE
enables this assembly to have unrestricted access within SQL Server.
Second, you have to create the stored procedure, which will basically invoke the stored procedure
you have created from the C# assembly.
CREATE PROCEDURE dbo.InsertCurrency_CS
(
@currCode nvarchar(3),
@currName nvarchar(50)
)
AS EXTERNAL NAME SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS;
GO
The name used in
CREATE PROCEDURE
is the name (
InsertCurrency_CS
) you gave to the function in C#
class (refer to Listing 20-1). Next you set the input parameters that are being passed to the C# function
(refer Listing 20-1).
The external name is actually in the syntax of
<SQL registered assembly>.<CS class name>.<CS
function name>
, so it turns out to be as follows:
SQLCLR_StoredProcedure
.
StoredProcedures
.
InsertCurrency_CS
Refer to Listings 20-1 and 20-2 for the class name, assembly name, and so on, which are in use here.
Executing the SQL CLR Stored Procedure
After deploying the assembly and creating a stored procedure, you are ready to execute this procedure
from SQL 2012 and insert currency into the AdventureWorks.Sales.Currency table.
Try It: Executing the SQL CLR Stored Procedure
In this exercise, you will execute the
InsertCurrency_CS
stored procedure.
1.
Open SQL Server Management Studio (if not already open), select SQL2012db
and click the New Query button.
2.
In the query window, add the code shown in Listing 20-3 to execute the
procedure and add a currency.
