CODEWITHSUNDEEP
sqlsql-serveruser-defined-functions
user420667
user420667

Reputation: 6700

Is it possible to use both a filter and a view functions in the same query?

My tables are a bit like this (please excuse the inexact definitions):

CREATE TABLE JunctionTable( int Concrete1ID, int Concrete2ID, int someProperty)
CREATE TABLE Concrete1 ( int ID, int Prop1, int Prop2)
CREATE TABLE Concrete2 ( int ID, int Prop1, int Prop2, int Prop3, int Prop4)

There are foreign keys to each of the concrete tables.

And I have some functions. One function - FilterJunction - to get things in the JunctionTable that meet certain conditions, and another function - SomeViewofConcrete2 - to return properties of a Concrete2 row given a Concrete2ID.

CREATE FUNCTION [dbo].FilterJunction() Returns Table AS
BEGIN RETURN (SELECT * FROM JunctionTable WHERE someProperty < 5) 
END

CREATE FUNCTION [dbo].SomeViewOfConcrete2(@ID int) RETURNS TABLE
BEGIN
  Return (SELECT Prop2, Prop4 FROM Concrete2 WHERE ID = @ID)
END

Note that SomeViewOfConcrete2 is expected to return just one row at most (and in this case, it should definitely return one row, as I've setup the appropriate foreign keys.)

So finally I'd like to run the following query:

SELECT ConcreteID1, ConcreteID2, [dbo].SomeViewOfConcrete2(Concrete2ID)
FROM [dbo].FilterJunction()

But get the following error:

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SomeViewOfConcrete2", or the name is ambiguous."

Though if I try to run SomeViewOfConcrete2 separately with a specific ConcreteID2, that query runs perfectly fine. (e.g. SELECT * FROM [dbo].SomeViewOfConcrete2(1)

I think the problem is that I have no way to tell SQL that the function returns exactly one row, so it doesn't know how to give the expected result set of ConcreteID1, ConcreteID2, Prop2, Prop4, but that error message sure doesn't give a good indication of that.

One way I could solve this problem is to just do:

SELECT ConcreteID1, ConcreteID2, Prop2, Prop4
FROM [dbo].FilterJunction() F
JOIN CONCRETE2 C
ON F.ConcreteID2 = C.ID

But that doesn't use the SomeViewOfConcrete2 function, and thus doesn't encourage code reuse.

So I apologize but my question is a bit two fold:

  1. Is it possible to design the query in a way that encourages code reuse of both the filter and the viewing function?
  2. Why is SQL throwing the error it gives and not saying something else?

Thanks.

Upvotes: 2

Views: 53

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

This is because a table valued function is designed to return any number of rows. You can't just shove a table like this as a column. You can however use APPLY here quite effectively.

SELECT ConcreteID1
    , ConcreteID2
    , svc.Prop2 --or whatever columns from that function
    , svc.Prop4
FROM [dbo].FilterJunction() fj
cross apply [dbo].SomeViewOfConcrete2(fj.Concrete2ID) svc

Upvotes: 4

Related Questions