CLR存储过程

为什么80%的码农都做不了架构师？>>>   

C# 代码

namespace SayHello
{

    using Microsoft.SqlServer.Server;

    public class SayHelloClass
    {

        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SayHello(out string greeting)
        {

            SqlMetaData colInfo = new SqlMetaData("问候", System.Data.SqlDbType.NVarChar, 80);

            SqlDataRecord greetingRecord = new SqlDataRecord(new SqlMetaData[] { colInfo });


            greetingRecord.SetString(0, "你好，这是 CLR 存储过程练习");

            SqlContext.Pipe.Send(greetingRecord);

            greeting = string.Format("现在时间是 {0} -- 达意科技", DateTime.Now.ToString());
        }

    }
}    

建立存储过程

use AIMS;
go

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 'f:\zp\SayHello.dll';
go

create procedure dbo.SayHello
(
	@Greeting nvarchar(80) output
)
AS EXTERNAL NAME SayHello.[SayHello.SayHelloClass].SayHello;
GO

use aims;
go

exec sp_configure 'clr_enabled','1';
go

reconfigure;
go

使用方法

USE [AIMS]
GO

DECLARE	@return_value int,
		@Greeting nvarchar(80)

EXEC	@return_value = [dbo].[SayHello]
		@Greeting = @Greeting OUTPUT

SELECT	@Greeting as N'Greeting', @return_value as return_value

GO

注意事项

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

转载于:https://my.oschina.net/pp219/blog/679807