Calling a Table Valued MSSQL Function from C#

[Abhishek Srivastava]

Hello All,

I am trying to call a Function in SQL Server which returns a TABLE type.

I get an exception

Unhandled Exception: System.Data.Sql Client.SqlExcep tion: The request for
procedu
re 'testFunc' failed because 'testFunc' is a function object.
at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
cmdBehavior
, RunBehavior runBehavior, Boolean returnStream)
at System.Data.Sql Client.SqlComma nd.ExecuteReade r()
at CallSqlFunc.Mai n(String[] args)
The C# code which I have written is

using System;
using System.Data;
using System.Data.Sql Client;

public class CallSqlFunc
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection(@ "server=nt22914 1;trusted_conne ction=yes;datab ase=junk");
SqlCommand cmd = new SqlCommand("tes tFunc", con);
cmd.CommandType = CommandType.Sto redProcedure;
con.Open();
SqlDataReader reader = cmd.ExecuteRead er();
while(reader.Re ad())
{
Console.WriteLi ne(reader.GetIn t32(0));
}
con.Close();
}
}
The SQL Server Function code is

create Function testFunc ()
returns @junktable table (num int)
as
begin
insert @junktable values (1)
insert @junktable values (2)
return
end

Why doesn't this code work? I am able to call Scalar SQL Server
functions. The problem comes only when trying to call TVFs.

Thanks in advance for your help.

regards,
Abhishek.

[Nicholas Paldino [.NET/C# MVP]]

Abhishek,

This is just a guess, but you might have to issue a select against the
function, something like:

select * from dbo.testFunc()

This should return to you the function itself. When I run this in query
analyzer, it gives me the result set as I would expect.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- nick(dot)paldin o=at=exisconsul ting<dot>com

"Abhishek Srivastava" <ab******@nospa m.net> wrote in message
news:uo******** ******@TK2MSFTN GP11.phx.gbl...

Hello All,

I am trying to call a Function in SQL Server which returns a TABLE type.

I get an exception

Unhandled Exception: System.Data.Sql Client.SqlExcep tion: The request for
procedu
re 'testFunc' failed because 'testFunc' is a function object.
at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
cmdBehavior
, RunBehavior runBehavior, Boolean returnStream)
at System.Data.Sql Client.SqlComma nd.ExecuteReade r()
at CallSqlFunc.Mai n(String[] args)
The C# code which I have written is

using System;
using System.Data;
using System.Data.Sql Client;

public class CallSqlFunc
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection(@ "server=nt22914 1;trusted_conne ction=yes;datab ase=junk");
SqlCommand cmd = new SqlCommand("tes tFunc", con);
cmd.CommandType = CommandType.Sto redProcedure;
con.Open();
SqlDataReader reader = cmd.ExecuteRead er();
while(reader.Re ad())
{
Console.WriteLi ne(reader.GetIn t32(0));
}
con.Close();
}
}
The SQL Server Function code is

create Function testFunc ()
returns @junktable table (num int)
as
begin
insert @junktable values (1)
insert @junktable values (2)
return
end

Why doesn't this code work? I am able to call Scalar SQL Server
functions. The problem comes only when trying to call TVFs.

Thanks in advance for your help.

regards,
Abhishek.