C# sql stored procedure

I am a rookie in writing SQL stored procedure using C#. Jus开发者_Go百科t wandering is there any way I could create a general method which will be called in the stored procedure within the same script?

Here a page for getting started using Sql Server stored procs for the CLR.

And here's a Hello World example to get you started.

The idea is that you write complex logic using a higher-level object oriented language and then just have simple calls from your stored procs in Sql Server execute that logic in the CLR.

You can create a UDF in the stored procedure that executes based on a parameter sent to the stored procedure:

I.E. (in your SP)

DECLARE @var1 int
IF @param1 = 1 THEN
   SELECT @var1 = dbo.fn_MyUDF(@params)
END IF

CLR procedure are just like any other stored procedure, and same goes for functions. Once deployed in the database, they are accessible to your script and your queries. Say you create a C# method in a class:

class Foo
{
 public static void Bar()
 {
   // Awesome code goes here
 }
}

You can compile this class into an assembly DLL, say MyClasses.DLL, then load the aseembly into the database using CREATE ASSEMBLY MyClasses FROM, declare the procedure with CREATE PROCEDURE Bar EXTERNAL MyClasses.Foo.Bar. then your script can simply invoke the procedure with EXEC Bar;.

Visual Studio can simplify this whole process, the SQL Server project type can deploy the assembly and declare the procedures/functions/data types contained.

There are more details once you start to digg in, like the proper use of attributes like SqlProcedureAttribute or SqlFunctionAttribute in your C# code etc etc.

To get started, I recommend some MSDN articles like Using CLR Integration in SQL Server 2005, Common Language Runtime (CLR) Integration Programming Concepts and have a look at the samples in Usage Scenarios and Examples for Common Language Runtime (CLR) Integration.