笔记167 实现CLR存储过程

笔记167 实现CLR存储过程

--实现CLR存储过程
IF EXISTS (SELECT  * FROM sys.procedures WHERE [name]='SayHello')
DROP PROCEDURE SayHello
GO
IF EXISTS (SELECT *FROM sys.assemblies WHERE [name]='SayHello')
DROP ASSEMBLY SayHello
GO
CREATE ASSEMBLY SayHello
FROM 'D:\C# pratice\SayHello\SayHello\bin\Debug\SayHello.dll'
WITH permission_set=SAFE
GO

CREATE PROCEDURE SayHello
(
     @Greeting NVARCHAR( 80) OUTPUT
)
AS EXTERNAL NAME SayHello.[SayHello.SayHelloClass].SayHello;
go
    
USE Northwind
GO

EXEC sys.sp_configure  @configname = 'clr enabled', -- varchar(35)
    @configvalue = 1 -- int
GO
RECONFIGURE
GO

DECLARE @return_value INT ,@Greeting NVARCHAR( 80);

--执行CLR存储过程并取得传回值与输出变量的值
EXEC @return_value=SayHello
@Greeting=@Greeting OUTPUT

--检索传回值与输出变量的值
SELECT @return_value AS 传回值, @Greeting AS "输出参数 @Greeting 之值";