function problem, referring to another function

I'm trying to create a couple of functions and am running into a problem
that I cannot explain. This uses Northwind database.
First of all, this one works:
---
ALTER FUNCTION fnCustomers
(
@.Initial VARCHAR(1)
)
RETURNS @.Cust TABLE
(
CustomerID VARCHAR(100)
)
AS
BEGIN
INSERT @.Cust
SELECT CustomerID from Customers where CustomerID LIKE '%' + @.INITIAL +
'%'
RETURN
END
---
and I can execute this:
SELECT * from dbo.fnCustomers('A')
and get results. Cool.
Now I want to extend this with another function that counts the results from
the first function:
---
ALTER FUNCTION fnCustomersCount
(
@.Initial VARCHAR(1)
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @.CustCount INT
SELECT @.CustCount = CustomerID
FROM fnCustomers(@.Initial)
GROUP BY CustomerID
RETURN @.CustCount
END
---
But thatdoesn't seem to work.
Select * from fnCustomersCount ('A')
gives me an error: Invalid object name 'fnCustomersCount'.
what am I doing wrong?Hello, mrmagoo
When invoking scalar UDF-s, you must use the UDF in an expression (not
in the FROM clause) and you must specify the object owner, like this:
SELECT dbo.fnCustomersCount ('A')
And by the way, why does your function return a varchar(10) instead of
an int ?
Razvan|||Try dbo.fnCustomersCount
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"mrmagoo" <-> wrote in message
news:eHB%23no%23SGHA.4900@.TK2MSFTNGP09.phx.gbl...
> I'm trying to create a couple of functions and am running into a problem
> that I cannot explain. This uses Northwind database.
> First of all, this one works:
> ---
> ALTER FUNCTION fnCustomers
> (
> @.Initial VARCHAR(1)
> )
> RETURNS @.Cust TABLE
> (
> CustomerID VARCHAR(100)
> )
> AS
> BEGIN
> INSERT @.Cust
> SELECT CustomerID from Customers where CustomerID LIKE '%' + @.INITIAL +
> '%'
> RETURN
> END
> ---
> and I can execute this:
> SELECT * from dbo.fnCustomers('A')
> and get results. Cool.
> Now I want to extend this with another function that counts the results
> from
> the first function:
> ---
> ALTER FUNCTION fnCustomersCount
> (
> @.Initial VARCHAR(1)
> )
> RETURNS VARCHAR(10)
> AS
> BEGIN
> DECLARE @.CustCount INT
> SELECT @.CustCount = CustomerID
> FROM fnCustomers(@.Initial)
> GROUP BY CustomerID
> RETURN @.CustCount
> END
> ---
> But thatdoesn't seem to work.
> Select * from fnCustomersCount ('A')
> gives me an error: Invalid object name 'fnCustomersCount'.
> what am I doing wrong?
>
>|||Thanks.
You're right. Originally it was returning an INT. I played around with it
and forgot to change it back.
Appreciate your help!
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1142837800.513726.290670@.i39g2000cwa.googlegroups.com...
> Hello, mrmagoo
> When invoking scalar UDF-s, you must use the UDF in an expression (not
> in the FROM clause) and you must specify the object owner, like this:
> SELECT dbo.fnCustomersCount ('A')
> And by the way, why does your function return a varchar(10) instead of
> an int ?
> Razvan
>|||Hi
I don't think you need the second UDF. I think you would like to count the
customers , am I right?
ALTER FUNCTION fnCustomers
(
@.Initial VARCHAR(1)
)
RETURNS @.Cust TABLE
(
CustomerID VARCHAR(100)
)
AS
BEGIN
INSERT @.Cust
SELECT CustomerID from Customers where CustomerID LIKE @.INITIAL +'%'
RETURN
END
SELECT COUNT(*) FROM dbo.fnCustomers('A%')
"mrmagoo" <-> wrote in message
news:OM0BMx%23SGHA.4608@.tk2msftngp13.phx.gbl...
> Thanks.
> You're right. Originally it was returning an INT. I played around with it
> and forgot to change it back.
> Appreciate your help!
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1142837800.513726.290670@.i39g2000cwa.googlegroups.com...
>