笔记167 实现CLR存储过程
1 --实现CLR存储过程
2 IF EXISTS (SELECT * FROM sys.procedures WHERE [name]='SayHello')
3 DROP PROCEDURE SayHello
4 GO
5 IF EXISTS (SELECT *FROM sys.assemblies WHERE [name]='SayHello')
6 DROP ASSEMBLY SayHello
7 GO
8 CREATE ASSEMBLY SayHello
9 FROM 'D:\C# pratice\SayHello\SayHello\bin\Debug\SayHello.dll'
10 WITH permission_set=SAFE
11 GO
12
13 CREATE PROCEDURE SayHello
14 (
15 @Greeting NVARCHAR( 80) OUTPUT
16 )
17 AS EXTERNAL NAME SayHello.[SayHello.SayHelloClass].SayHello;
18 go
19
20 USE Northwind
21 GO
22
23 EXEC sys.sp_configure @configname = 'clr enabled', -- varchar(35)
24 @configvalue = 1 -- int
25 GO
26 RECONFIGURE
27 GO
28
29 DECLARE @return_value INT ,@Greeting NVARCHAR( 80);
30
31 --执行CLR存储过程并取得传回值与输出变量的值
32 EXEC @return_value=SayHello
33 @Greeting=@Greeting OUTPUT
34
35 --检索传回值与输出变量的值
36 SELECT @return_value AS 传回值, @Greeting AS "输出参数 @Greeting 之值";
