.NET CLR Programming with SQL Server 2005 Made Simple - .NET CLR stored procedure in SQL Server 2005 database: Coding

(Page 3 of 6 )

Before proceeding through this section, make sure that you have completed everything in the previous section.

Once the “IncSalaries.vb” is created, modify your code in such a way that it looks something like the following:

ImportsSystem

ImportsSystem.Data

ImportsSystem.Data.SqlClient

ImportsSystem.Data.SqlTypes

ImportsMicrosoft.SqlServer.Server

 

 

PartialPublic Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub IncSalaries(ByVal IncVal As SqlDouble)

        ' Add your code here

        Using cn As New SqlConnection("context connection=true")

            Dim cmd As New SqlCommand("update sample.dbo.emp set
sal = sal + " & IncVal.ToString, cn)

            cmd.Connection.Open()

            cmd.ExecuteNonQuery()

        End Using

    End Sub

EndClass

The above is a simple CLR based stored procedure written using Visual Basic.NET 2005 to get it deployed in SQL Server 2005.  The name of the stored procedure is “IncSalaries;” it accepts a single parameter, “IncVal.” The above stored procedure simply increases the salaries of every employee in the table “emp” with the value provided as the parameter.

Next: .NET CLR stored procedure in SQL Server 2005 database: Testing >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: .NET CLR Programming with SQL Server 2005 Made Simple

· Hello guys! This is a simple all-in-one contribution in dealing with .NET CLR...   >>> Post your comment now!