.NET 1.1 LLBLGEN 2.0 v1.1.4322 SQL Server 2000

I'm wondering if there is any way to use a user defined function that returns a table variable. I basically wanted something like a view, but one that I can pass parameters to. It would be great if a udf that returned a table would work like a view and return a datatable.

Quick question. Why would you not use a stored procedure for this?

At the moment it's not possible to use table functions, only scalar functions. So you can't use them in joins nor subqueries at the moment. The only way to utilize them at the moment is via custom SQL and a projection. So you could formulate a RetrievalQuery like how it's done for a retrieval proc in the generated code, and pass that to FetchProjection or FetchDataReader, to get the data out.

bclubb wrote:

Quick question. Why would you not use a stored procedure for this?

The primary reason why I like a udf that returns a table over a stored procedure is that a udf can be queried like a table where a stored procedure cannot. I can't say select * from storedprocedure where something = something. This functionality makes a udf a lot easier to use. I would just say in the end the a udf is more easily re-used the a stored procedure is. Maybe I'm wrong, but I have had a number of occasions where the stored procedure just didn't work out very well and a udf did.

I wonder why a udf that returns a table isn't handled just like a stored procedure by LLBLGen as I would expect it to return a datatable just like a stored procedure.

Aaron

That's understandable. We do the same thing on our side to simplify many queries using joins. I thought you were just trying to call the udf.

You can't call a UDF from ADO.NET directly, you have to emit code like: SELECT * FROM udf()

So a UDF isn't a first class citizen as a proc is and can't be threated the same.

Thanks everyone for the comments. I just went with a view so it's easily re-usable and I can pass a RelationPredicateBucket into it for my where clause. This is good for now.

Aaron