Using a User defined function returning a table in a subquery

Using a User defined function returning a table in a subquery

Post by Alistair Welchma » Sat, 13 Dec 2003 18:37:33



I have a user defined function taking a UUID as a parameter and returning a
table


uniqueidentifier  )

RETURNS TABLE AS

RETURN

select  *
from Messages

of other stuff

I want to use this in the following way:

SELECT    DeploymentID, ( select count(*) from
dbo.fnDeploymentGetAllInComing ( DeploymentID ) )
FROM       Deployment

and I get this error:

'DeploymentID' is not a recognized OPTIMIZER LOCK HINTS option.

or another (syntax) error if I qualify the argument name:

Incorrect syntax near '.'

Is this kind of behavoir forbidden, although it's not a problem if I invoke
a (scalar) function on a column argument:

select value, dbo.fnSomeScalarFunction(value)
from table

Can anyone explain?

 
 
 

Using a User defined function returning a table in a subquery

Post by Vishal Parka » Sat, 13 Dec 2003 19:30:54


Alistair,

You can not call a udf that returns table in the SELECT clause. an alternative
with you would be, rather passing DeploymentID as a parameter modify your
function which will return you the counts for each DeploymentID

See following example.
Ex:
USE pubs
GO

RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t

      t.title_id = s.title_id)

--normal call
select count(*) from salesbystore(6380)

--above function would be modified as follows and refer this function in the
query to get the counts.

CREATE FUNCTION SalesByStore1 ()
RETURNS TABLE
AS
RETURN (
select count(*) [count], stor_id from
(SELECT title, qty, stor_id
      FROM sales s, titles t
      WHERE t.title_id = s.title_id) X
group by stor_id
)

--call above function

select distinct a.stor_id, b.[count]
from sales a, SalesByStore1()  b
where a.stor_id = b.stor_id
and a.stor_id = 6380

--
- Vishal