.NET CLR Programming with SQL Server 2005 Made Simple - .NET CLR user-defined function in SQL Server 2005 database
(Page 5 of 6 )
Now we shall extend the previous sections to add a stored function to our existing solution.
- Using the “Solution explorer,” right click on the “SampleAllInOne” Project and go to Add-> User-defined Function (to create a new stored function) (Fig 14).
- You will be presented with the “Add New Item” dialog box. Within that dialog box, select “User defined function” as the template, provide the name “getNetSalary” and finally click on “Add” (Fig 15).
Once the “getNetSalary.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 UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction
(DataAccess:=DataAccessKind.Read)> _
Public Shared Function getNetSalary(ByVal empno As SqlString)
As SqlDouble
' Add your code here
Dim sal As Double
Using cn As New SqlConnection("context connection=true")
Dim cmd As New SqlCommand("select sal from
sample.dbo.emp where empno='" & empno.ToString & "'", cn)
cmd.Connection.Open()
sal = CType(cmd.ExecuteScalar, Double)
cmd.Dispose()
End Using
Dim hra As Double = sal * 10 / 100
Dim ta As Double = 200
Dim gross As Double = sal + hra + ta
Dim epf As Double = sal * 5 / 100
Dim net As Double = gross - epf
Return net
End Function
EndClass
To test the above stored procedure, follow the following steps:
- Using “Solution Explorer,” open “test.sql” in “test scripts” (Fig 11).
- Go to the last line and modify the code as follows:
--select 'To run your project, please edit the Test.sql file in
your project. This file is located in the Test Scripts folder in
the Solution Explorer.'
--exec IncSalaries 50
select dbo.getNetSalary(empno) from sample.dbo.emp
Press F5 to execute the function and you should be able to see the result.
Next: .NET CLR user-defined aggregate in SQL Server 2005 database >>
More MS SQL Server Articles
More By Jagadish Chaterjee
