How to Add SQL Server Built-in/User Defined Function in Typed DataSet as Queries

Issue: SQL Server Function in Typed DataSet does NOT return value
Level: Intermediate
Knowledge Required:

• T-SQL
• SQL User Defined Function
• Typed DataSet
• TableAdapter

Introduction:
This article explains how to add the SQL Server’s User Defined or Built-in Function in Typed DataSet. Note that if we do NOT follow the proper way then Function might NOT return any value in Visual Basic.

Description:
As we have used the Typed DataSets in VB 2005, which are primarily used to store the data, loaded from Database. We can also use the Typed DataSets to execute SQL Server functions (either built-in or user Defined).

For this purpose first I will show you the normal procedure:

1) Add any User Defined Function in the Database
e.g.: The following function returns the server date

CREATE FUNCTION [dbo].[GetServerDate] ()
RETURNS DateTime
AS
BEGIN
    DECLARE @Result AS DateTime;

    SELECT @Result = GetDate();

    RETURN @Result;
END

2) In Visual Studio 2005, create a new Windows Application Project
3) Add a New Empty DataSet (Data->Add New Data Source) (DO NOT select any tables/procedures/etc.)
4) Open the DataSet in Designer
5) Right Click in the Designer and Click on Add->Query


6) In the TableAdapter Query Configuration Wizard, select the "Use existing stored procedure" option (on page 2)


7) In the next step select the GetServerDate procedure from given List


8) Click Finish

This will add a Query TableAdapter in our DataSet and have added a Function GetServerDate which can be called using Code as,

Dim adp As MyDataSetTableAdapters.QueriesTableAdapter
Dim o As Object

adp = New MyDataSetTableAdapters.QueriesTableAdapter
o = adp.GetServerDate()

But here adp.GetServerDate() will always return Nothing. I think this is because internally when TableAdapter executes the Procedure it does NOT pass the parameters properly.

To overcome this issue we will use a slightly different approach.

Proceed the above given steps up to Step 5, then:

6) In the TableAdapter Query Configuration Wizard, this time select "Use SQL Statements" option, click Next
7) Select "SELECT which returns a single value" option, click Next


8) In the next step type the following SQL Query and click Next

SELECT dbo.GetServerDate()

9) Next we will supply the Function Name (which will be used in coding), type GetServerDate here
10) Click Finish

Now we will again test the function with same code given above and you will notice it returns a Value i.e. DateTime on Server.

In the above example we have used the SQL Server User Defined Function, we can also use the SQL Server's Built-in Function. For Example in the above steps, replace the SQL Statement in Step 8 to the following SQL Statement.

SELECT Is_Member(@Role)

Above function will check the Current Login in the Particular Role.