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.SqlClient.SqlException: The request for
procedu
re 'testFunc' failed because 'testFunc' is a function object.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior
, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at CallSqlFunc.Main(String[] args)


The C# code which I have written is

using System;
using System.Data;
using System.Data.SqlClient;

public class CallSqlFunc
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection(@"server=nt229141;trusted_connection=yes;database=junk");
SqlCommand cmd = new SqlCommand("testFunc", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
Console.WriteLine(reader.GetInt32(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.