SQL Server CLR calls DLL T-SQL and CLR type does not match

1. Create the sqlserverclrcallnet project in vs2005 and create clrfunction. CS

View code

Using System;
Using System. Collections. Generic;
Using System. text;
Using Microsoft. sqlserver. server;
Using System. Data. sqlclient;
Using System. Data. sqltypes;

namespace sqlserverclrcallnet
{< br> Public class clrfunction
{< br> [Microsoft. sqlserver. server. sqlprocedure ()]
Public static void getresultsetstring ( sqlstring SnO) /// Differences
{
sqlcommand cmd;

using (sqlconnection conn = New sqlconnection (" context connection = true " )
{< br> Conn. open ();
cmd = New sqlcommand (" select ID, name, modifieddate from changehistory where name = @ SnO ", Conn);

Sqlparameter para =NewSqlparameter ("@ SnO", System. Data. sqldbtype. varchar );//Differences
Para. value = SnO;
Cmd. Parameters. Add (para );

Sqlcontext. Pipe. executeandsend (CMD );
}
}

[Microsoft. sqlserver. server. sqlprocedure ()]
Public static void getresultsetint ( int SnO) /// Differences
{
sqlcommand cmd;

using (sqlconnection conn = New sqlconnection (" context connection = true " )
{< br> Conn. open ();
cmd = New sqlcommand (" select ID, name, modifieddate from changehistory where id = @ SnO ", Conn);

Sqlparameter para =NewSqlparameter ("@ SnO", System. Data. sqldbtype. INT );//Differences
Para. value = SnO;
Cmd. Parameters. Add (para );

Sqlcontext. Pipe. executeandsend (CMD );
}
}

}
}

 

 

2. Place the DLL in the root directory of the server C.

Iii. Execute the following SQL statement

 

Create Assembly insertcurrency From ' C: \ sqlserverclrcallnet. dll '
With Permission_set = SAFE;
Go

--Drop procedure insertcurrency_int
Create ProcedureDBO. insertcurrency_int (@ SnO Int)
AsExternal name insertcurrency.[Sqlserverclrcallnet. clrfunction]. Getresultsetint;
Go 

ExecInsertcurrency_int'1'

Go
--Drop procedure insertcurrency_string
Create ProcedureDBO. insertcurrency_string (@ SnO Nvarchar(200))
AsExternal name insertcurrency.[Sqlserverclrcallnet. clrfunction]. Getresultsetstring;

Go
ExecInsertcurrency_string'Landor'

 

 

ID	Name	Modifieddate
1	Landor

 

 

Why should I use. Net to develop the store procedure?

T-SQL stored procedure and SQL CLR stored procedure it is not necessarily in the performance of the selection, but the development of the choice of accessibility, for example, to format the string, the T-SQL can not do, be sure to use the SQL clr udf, if you want to access the case/AD/other external resources, the T-SQL is also very difficult to implement, this time in addition to SQL CLR, please do not try it.

Another factor is the degree of parallelism of design, such as progressive loop (for)/cursor) or other T-SQL can not do or must be in the phase when the T-SQL to implement the program, you can use SQL CLR to handle. http://social.msdn.microsoft.com/Forums/zh-TW/240/thread/c80255a7-5cf2-4a54-b9c1-995098c18a0b