CLR存储过程

C# 代码

1namespace SayHello
2{
3
4    using Microsoft.SqlServer.Server;
5
6    public class SayHelloClass
7    {
8
9        [Microsoft.SqlServer.Server.SqlProcedure]
10        public static void SayHello(out string greeting)
11        {
12
13            SqlMetaData colInfo = new SqlMetaData("问候", System.Data.SqlDbType.NVarChar, 80);
14
15            SqlDataRecord greetingRecord = new SqlDataRecord(new SqlMetaData[] { colInfo });
16
17
18            greetingRecord.SetString(0, "你好，这是 CLR 存储过程练习");
19
20            SqlContext.Pipe.Send(greetingRecord);
21
22            greeting = string.Format("现在时间是 {0} -- 达意科技", DateTime.Now.ToString());
23        }
24
25    }
26}    

建立存储过程

1use AIMS;
2go
3
4if exists(select * from sys.procedures where [name] = 'SayHello')
5drop procedure SayHello;
6go
7
8if exists(select * from sys.assemblies where [name] = 'SayHello')
9drop assembly SayHello;
10go
11
12create assembly SayHello
13from 'f:\zp\SayHello.dll';
14go
15
16create procedure dbo.SayHello
17(
18	@Greeting nvarchar(80) output
19)
20AS EXTERNAL NAME SayHello.[SayHello.SayHelloClass].SayHello;
21GO
22
23use aims;
24go
25
26exec sp_configure 'clr_enabled','1';
27go
28
29reconfigure;
30go

使用方法

1USE [AIMS]
2GO
3
4DECLARE	@return_value int,
5		@Greeting nvarchar(80)
6
7EXEC	@return_value = [dbo].[SayHello]
8		@Greeting = @Greeting OUTPUT
9
10SELECT	@Greeting as N'Greeting', @return_value as return_value
11
12GO

注意事项

注意SQLServer支持的 .Net Framework 版本，查看方法: Microsoft SQL Server Management Studio -> 关于