Detailed introduction to creating CLR stored procedures in SQL Server 2005 _mssql

The creation of stored procedures prior to 2005 was done in a Transact-SQL language in the database, but now that SQL Server 2005 supports writing CLR stored procedures in other object-oriented languages, there are many official explanations for the benefits of this. Here is a direct description of the implementation method.

Suppose the server has a test database, the database has a schema user, there is a table test1, and then there is a SQL login user called Test_user, the user is set to VS2005 the database connected to the landing user.

Create a project in VS2005, the category is a SQL Server database project, and then add a stored procedure to the project. Then write the following stored procedure code in the file:

Copy Code code as follows:

Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SelectAll ()
{
using (SqlConnection connection = new SqlConnection ("Context connection=true"))
{
Connection. Open ();
SqlCommand command = new SqlCommand ("SELECT * from [user].test1", connection);

SqlContext.Pipe.ExecuteAndSend (command);
}
}
};

Save the code above and name it TestProcedure.cs file. Then there are 2 ways to add this stored procedure to the database.
1. Open. NET, and then compile TestProcedure.cs into a DLL assembly TestProcedure.dll
Csc/t:library/out:testprocedure.dll TestProcedure.cs then open the database test and click on the "Add new assembly" item in the right menu of the assembly in the database. Then browse to the TestProcedure.dll assembly that you just compiled. This adds the assembly, then creates a new SQL query, in which a stored procedure is created as follows: Create PROCEDURE [user]. Selectallas EXTERNAL NAME TestProcedure.StoredProcedures.SelectAllGO executes the above query statement, a CLR stored procedure is successfully added to the database.
Note that the test stored procedure above has no parameters, and if so, the stored procedure prototype in the database must be the same as the prototype in the CLR language. It is recommended that you create CLRC stored procedures using this method.

2. Create a stored procedure for SQL using VS. Writing a debug script to add to the project, the script can simply execute the stored procedure. The above project is then compiled and built and then deployed, and you can start debugging to complete it. VS automatically adds the appropriate stored procedure to the database. But the first approach is more flexible and reliable. In addition, use the 2nd method if an error occurs: The user failed to execute the stored procedure sp_enable_sql_debug this could be due to:

• Connectivity issues. A stable connection to the server is required.

• Lack of necessary permissions on the server. to debug on SQL Server 2005, the account that is running Visual Studio and the account that is used to connect to SQL Server must be a member of the sysadmin role. The account that is used to connect to SQL Server is either a Windows user account (if you are using Windows authentication), or an account with a user ID and password (if you are using SQL Authentication).

That means the test_user must be a sysadmin role. The above is the entire contents of the creation of the CLR stored procedure.