Testing table-valued functions

[ioani]

Hi,

I have a function that takes two arguments @arg1, @arg2 and and
returns a table based on these arguments. I tried to test it with
Query fixture but it does not work:

!|Query|EXEC MyFunction @arg1, @arg2 |
|column1|column2|column3|
|111 |222 | 333 |

I received a lot of errors. One of them:

System.Reflection.TargetInvocationException: Exception has been thrown
by the target of an invocation. --->
System.Data.SqlClient.SqlException: The request for procedure
'MyFunction ' failed because 'MyFunction ' is a table valued function
object.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning
(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader
.....................................................................................................

How can I test table-valued functions.

Thanks

[Gojko Adzic]

Hi Ioani,

SQL server magic is not one of my best skills, but if you create a
very simple function that demonstrates the problem and send it to me I
can try to work around it. Which version of DbFit are you using?

gojko

[ioani]

Hi,

I am using DBFit 20080822 version and SQL Server 2005.

An example to demonstrate the problem:

CREATE TABLE [dbo].[Tab](
col1 INT NULL,
col2 INT NULL
) ON [PRIMARY]

and a function:

CREATE FUNCTION [dbo].[MyFct](@arg AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT col1, col2
FROM Test.Tab
WHERE col1 > @arg
)

The DBFit test:

!|Set Parameter|arg|100|

!|Query|EXEC dbo.MyFct @arg|
|col1|col2|
|200 |300 |

Thanks,
Ioani

[Solomon]

> !|Query|EXEC dbo.MyFct @arg|
> |col1|col2|
> |200 |300 |

Hello Ioani. The syntax that you are using is for Stored Procedures,
not User-Defined Functions. For functions, you can either do a SELECT
FuncName if it is a scalar-valued function or you can do a SELECT *
FROM FuncName if it is a table-valued function (since it acts like a
table). Also, for functions you need to put parameters in
parenthesis. So, try the following:

SELECT col1, col2 FROM dbo.MyFct(@arg)

Hope that helps. Take care, Solomon..

[ioani]

Thanks a lot Solomon, it does work.

Ioani