ADO.NET incorrectly returns 0 from a SQL Server user-defined function (329497)

WORKAROUND

To work around this behavior, call the user-defined function in a Transact-SQL Select statement such as "Select dbo.[Function Name] (Parameters)" instead of calling the user-defined function directly.

Steps to Work Around the Behavior

Create a new user-defined function named Function1 in the Microsoft SQL Server Pubs database. To do this, run the following Transact-SQL command in Microsoft SQL Query Analyzer:
```
CREATE FUNCTION dbo.Function1()
RETURNS nvarchar(20) 
AS
BEGIN
   declare @out nvarchar(20)
   set @out = 'Hello World'
   RETURN @out
END
```
In Microsoft Visual Studio .NET, create a new Console Application project by using Visual Basic .NET. By default, Module1.vb is created.
Add the following namespaces at the beginning of Module1.vb:
```
Imports System
Imports System.Data.SqlClient
```

Add the following code to the Sub Main method in Module1.vb:

      ' Open a connection to the SQL Server Pubs database.
      Dim sqlConnection1 As New SqlConnection("Integrated Security=SSPI;data source=SERVERNAME;initial catalog=pubs;persist security info=True;")
      sqlConnection1.Open()

      ' Create a command object to call Function1.
      Dim sqlCommand1 As New SqlCommand()
      With sqlCommand1
         .CommandText = "Select dbo.[Function1]()"
         .CommandType = CommandType.Text
         .Connection = sqlConnection1
      End With

      Dim sqlDataReader1 As SqlDataReader
      ' Call Function1.
      sqlDataReader1 = sqlCommand1.ExecuteReader
      sqlDataReader1.Read()
      Dim strRetVal As String
      strRetVal = sqlDataReader1.Item(0)
      MsgBox("Return Value from UDF is = " & strRetVal)
      sqlDataReader1.Close()

Modify the connection string appropriately for your environment.
On the Debug menu, click Start to run the application. Notice that the SQL Server user-defined function returns Hello World.

MORE INFORMATION

Steps to Reproduce the Behavior

Create a user-defined function in Microsoft SQL Server as described in step 1 of the "Workaround" section.
In Visual Studio .NET, create a new Console Application project by using Visual Basic .NET. By default, Module1.vb is created.
Add the following namespaces at the beginning of Module1.vb:
```
Imports System
Imports System.Data.SqlClient
```

Add the following code to the Sub Main method in Module1.vb:

      ' Connect to the SQL Server Pubs database.
      Dim sqlConnection1 As New SqlConnection("Integrated Security=SSPI;data source=SERVERNAME;initial catalog=pubs;persist security info=True;")
      SqlConnection1.Open()

      ' Create a command object to call Function1.
      Dim sqlCommand1 As New SqlCommand()
      Dim sqlDataReader1 As SqlDataReader

      ' Function to call:
      sqlCommand1.CommandText = "dbo.[function1]"
      sqlCommand1.CommandType = System.Data.CommandType.Text
      sqlCommand1.Connection = sqlConnection1

      ' Add parameters to get the Return Value.
      sqlCommand1.Parameters.Add(New SqlClient.SqlParameter("@out", SqlDbType.NVarChar, 20))
      sqlCommand1.Parameters("@out").Direction = ParameterDirection.ReturnValue
      sqlCommand1.Parameters("@out").Value = "<NULL>"

      ' Call Function1.
      sqlDataReader1 = sqlCommand1.ExecuteReader()
      sqlDataReader1.Read()
      sqlDataReader1.Close()

      Dim strRetVal As String
      strRetVal = sqlCommand1.Parameters("@out").Value
      MsgBox("Return Value for @out = " & strRetVal)

Modify the connection string appropriately for your environment.
On the Debug menu, click Start to run the application.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

306574 HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual Basic .NET

309486 HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual Basic .NET

308051 PRB: Output Parameters Are Not Returned When You Run an ADO.NET Command in Visual Basic .NET

194792 HOWTO: Retrieve Values in SQL Server Stored Procedures with ADO

For more information, visit the following Microsoft Web sites:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/adonetprogmsdn.asp