Random Error Message: The request for [this] procedure failed because [it] is a table valued function object

While working on a project recently, I ended up needing to call a “Table-valued Function” in SQL Server. Had I been writing my process from scratch, I would probably not have chosen to use a Table-valued function, but I was asked to tap into previously existing code that was, so I didn’t have much choice. Initially, I didn’t think that this would be a problem, however, when I started trying to actually call a Table-valued Function like you would a stored procedure or a Scalar-valued Function, I ended up getting the following error:

The request for procedure ‘Procedure X’ failed because ‘Procedure X’ is a table valued function object
 
 

This error is actually pretty self-explanatory. You can’t call a Table-valued function from ASP.NET. The problem was … I needed to call one. I tried all kinds of things. My last resort was going to be to create a new stored procedure (which I CAN call from ASP.NET) whose only function is to turn around and call the Table-valued function. This did not seem to be a very effective solution to me, if for no other reason than that it would require editing the new stored procedure every time the Table-value function changed. Those kinds of things are easy to get out of sync.

I did search for this error on line and I didn’t find much in the way of solutions. I finally found something that worked though and I thought that I would write a quick blog post about it. In essence, Table-valued Functions are called in the same way that one would run a query against a table. In other words, you use the function as if it were a table. So, if we want to select all the episodes from the BurnNotice Episodes table from season 2 where the rating is 5 stars, then our query would look like this:

SELECT * FROM episodes WHERE rating = 5 AND season = 2

NOTE: This would return a list of all episodes from season 2 as they are all awesome

 

However, if the rating of a given episode were an aggregate of a variety of factors and happened to include several values from different places, we could write a Table-valued Function called “EpisodeRatings” accepts two parameters (@Rating and @Season) that returns all the values that we need. Rather than an EXEC statement which we would use for a Scalar-valued Function, our call would look like this:

SELECT * FROM episoderatings(5, 2)
 

Knowing this difference is actually the key to solving the error that is the subject of this post. Rather than try to call the Table-valued Function as if it were a stored procedure, we will build a parameterized text string in our C# code. Using a parameterized string allows you to dynamically build the SQL Statement in your C# code, but it also uses strongly typed input parameters which would thwart attempts at SQL Injection attacks. I don’t think that this is the ideal solution, but for one of those time in which the project’s requirements or prior design constrain your options, it certainly works. So, below I have an example of some code that will build the SQL String, add in the parameters and then execute the Table-valued Function and return a data table.

public static DataTable ExecuteTableValueFunction(string database, List<SprocParameter> parameterList)
{

	StringBuilder sb = new StringBuilder();

	// Build the parameterized sql string
	sb.Append("SELECT * ");
	sb.Append("FROM episoderatings(");
	sb.Append("@Rating");
	sb.Append(",@Season");
	sb.Append(")");

	DataTable dt = new DataTable();
	Database db = DatabaseFactory.CreateDatabase("BurnNotice");
	DbCommand cmd = db.GetSqlStringCommand(sb.ToString());

	cmd.CommandType = CommandType.Text;
	cmd.CommandTimeout = timeout;

	foreach (SprocParameter param in parameterList) {
		if (param.Value == null) {
			db.AddInParameter(cmd, param.Name, param.DataType, DBNull.Value);
		} else {
			db.AddInParameter(cmd, param.Name, param.DataType, param.Value);
		}
	}

	db.CreateConnection().Open();
	dt.Load(db.ExecuteReader(cmd));
	db.CreateConnection().Close();

	return dt;
}
 
 

So, to conclude … I must say that I did not love solving this issue in this manner. I don’t like building SQL in my code using strings, still it is parameterized and will guard against SQL Injection attacks. In the end, I thought that it was a better option that creating extra procedures simply to call the table valued function. If you are having this error and have no other choice than to barrel forward using a table valued function, I hope that this post helps you solve the problem.

2 Responses to “Random Error Message: The request for [this] procedure failed because [it] is a table valued function object

  1. Mark says:

    You handled the situation about as well as one could without creating additional parameters, so kudos. I ended up wrapping the TVF with a stored procedure instead, since ADO.NET plays so nicely with result sets from stored procs.

  2. Clemens says:

    Thanks for your post!
    This also works if you want to call a user defined function from a pass-through query in Access, which was what I was looking for.

Leave a Reply