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

Isenção de Responsabilidade para Conteúdo da KB Desativado

Este artigo foi escrito sobre produtos para os quais a Microsoft já não fornece suporte. Por conseguinte, este artigo é oferecido "tal como está" e não será mais atualizado.

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 "Select dbo.[Function Name] (Parameters)" instead of calling the user-defined function directly.

Steps to Work Around the Behavior

  1. 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
  2. In Microsoft Visual Studio .NET, create a new Console Application project by using Visual Basic .NET. By default, Module1.vb is created.
  3. Add the following namespaces at the beginning of Module1.vb:
    Imports System
    Imports System.Data.SqlClient
  4. 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()
  5. Modify the connection string appropriately for your environment.
  6. 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

  1. Create a user-defined function in Microsoft SQL Server as described in step 1 of the "Workaround" section.
  2. In Visual Studio .NET, create a new Console Application project by using Visual Basic .NET. By default, Module1.vb is created.
  3. Add the following namespaces at the beginning of Module1.vb:
    Imports System
    Imports System.Data.SqlClient
  4. 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)
  5. Modify the connection string appropriately for your environment.
  6. 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:
Propriedades

ID do Artigo: 329497 - Última Revisão: 19 de jun de 2014 - Revisão: 1

Comentários