According to Microsoft, Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.<\/p>\n
1. Create SQL Stored Procedure in visual stuido<\/strong> 2. Set Target Framework to “.Net 2.0”<\/strong> SQL 2008 only support .Net 2.0. Comply to DLL. EXEC\t@return_value = [dbo].[FirstListEmployees] SELECT\t@outputParameter as N'@outputParameter' According to Microsoft, Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the … Continue reading
\nProject name: “FirstSqlClass.cs”; class name:”FirstSqlClr” and function name:”ListEmployees” <\/p>\n\r\nusing System;\r\nusing System.Data;\r\nusing System.Data.SqlClient;\r\nusing System.Data.SqlTypes;\r\nusing Microsoft.SqlServer.Server; \r\n\r\n\r\npublic partial class FirstSqlClr\r\n{\r\n [Microsoft.SqlServer.Server.SqlProcedure]\r\n\r\n public static void ListEmployees(SqlString fLetter, SqlString orderBy, out SqlString outputParameter)\r\n {\r\n string sqlCmd = \"\";\r\n sqlCmd += \" select c.LastName, c.FirstName, c.EmailAddress \";\r\n sqlCmd += \" from Person.Contact as c inner join HumanResources.Employee as e on c.ContactID = e.ContactID \";\r\n sqlCmd += \" where left(c.LastName, 1) = @fLetter\";\r\n sqlCmd += \" order by \" + orderBy.ToString(); \r\n \r\n SqlContext.Pipe.Send(\"Stored Procedure started at: \" + DateTime.Now.ToString() + \"\\n\");\r\n using (SqlConnection conn = new SqlConnection(\"context connection = true\"))\r\n {\r\n conn.Open();\r\n SqlCommand cmd = new SqlCommand(sqlCmd, conn);\r\n cmd.Parameters.Add(new SqlParameter (\"@fLetter\", SqlDbType.NVarChar, 1));\r\n cmd.Parameters[0].Value = fLetter;\r\n \r\n SqlDataReader rdr = cmd.ExecuteReader();\r\n SqlContext.Pipe.Send(rdr);\r\n }\r\n outputParameter = \"after some manipulation that has to be done by a procedure language.\";\r\n SqlContext.Pipe.Send(\"Stored Procedure ended at: \" + DateTime.Now.ToString() + \"\\n\");\r\n } \r\n};\r\n<\/pre>\n
\n3. Enable Clr in Sql Server:<\/strong>
\n
\nsp_configure 'clr enabled', 1
\ngo
\nreconfigure
\ngo
\n<\/code>
\n4. Register the assembly in SQL Server<\/strong> Locate the Dll file and set Permission to “SAFE”.
\n
\nuse AdventureWorks
\ngo
\ncreate assembly FirstSqlClrClass from
\n'C:\\Users\\kwu\\Documents\\visual studio 2010\\projects\\FirstSqlClrClass\\FirstSqlClrClass\\bin\\Debug\\FirstSqlClrClass.dll'
\ngo
\n<\/code>
\n5. Create Stored Procedure<\/strong>
\n
\nuse AdventureWorks
\ngo
\ncreate procedure FirstListEmployees(@fLetter Nvarchar(1), @OrderBy NVarchar(20), @outputParameter NVarchar(100) OUT) as
\nexternal name FirstSqlClrClass.FirstSqlClr.ListEmployees
\ngo
\n<\/code>
\nThe the SP can be used as regular T-Sql stored procedure.
\n
\nuse AdventureWorks
\ngo
\nDECLARE\t@return_value int,
\n\t\t@outputParameter nvarchar(100)<\/p>\n
\n\t\t@fLetter = 'B',
\n\t\t@OrderBy = 'EmailAddress',
\n\t\t@outputParameter = @outputParameter OUTPUT<\/p>\n
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"