Microsoft KB Archive/329497

= ADO.NET incorrectly returns 0 from a SQL Server user-defined function =

Article ID: 329497

Article Last Modified on 3/19/2007

-

APPLIES TO


 * Microsoft ADO.NET 1.0

-



This article was previously published under Q329497



SYMPTOMS
When you call a Microsoft SQL Server user-defined function from a Microsoft ADO.NET application, the user-defined function returns 0 instead of the expected return value. This behavior occurs when you call a user-defined function by using SQL Server Managed Provider, OLE DB Managed Provider, or the ODBC Managed Provider.



WORKAROUND
To work around this behavior, call the user-defined function in a Transact-SQL Select statement such as &quot;Select dbo.[ ] &quot; 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(&quot;Integrated Security=SSPI;data source=SERVERNAME;initial catalog=pubs;persist security info=True;&quot;) sqlConnection1.Open

' Create a command object to call Function1. Dim sqlCommand1 As New SqlCommand With sqlCommand1 .CommandText = &quot;Select dbo.[Function1]&quot; .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(&quot;Return Value from UDF is = &quot; & 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.</ol>

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> Create a user-defined function in Microsoft SQL Server as described in step 1 of the &quot;Workaround&quot; section.</li> In Visual Studio .NET, create a new Console Application project by using Visual Basic .NET. By default, Module1.vb is created.</li>  Add the following namespaces at the beginning of Module1.vb: Imports System Imports System.Data.SqlClient </li>  Add the following code to the Sub Main method in Module1.vb: ' Connect to the SQL Server Pubs database. Dim sqlConnection1 As New SqlConnection(&quot;Integrated Security=SSPI;data source=SERVERNAME;initial catalog=pubs;persist security info=True;&quot;) SqlConnection1.Open

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

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

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

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

Dim strRetVal As String strRetVal = sqlCommand1.Parameters(&quot;@out&quot;).Value MsgBox(&quot;Return Value for @out = &quot; & strRetVal) </li> Modify the connection string appropriately for your environment.</li> On the Debug menu, click Start to run the application.</li></ol>

<div class="references_section">