stubborn user defined function problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stubborn user defined function problem

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2005-03-01 : 18:10:37

I have a stubborn problem with the use of a user defined function (udf). Any help would be appreciated as I dont seem to be able to see the cause of the problem.

Essentially, I am creating a membership system; I have a user table, and that user table can contain both "users" and "groups". A group membership table then contains records for users membership in a group. This information is then used to determine who has access to what.

I can use the function no problem. The problem I have is using the results of the function as part of a WHERE condition. I keep getting the following error: Incorrect syntax near '.'

Anyhow, heres the info to repro the problem:

-- create user table
CREATE TABLE [account] (
[accountid] [int] IDENTITY (1, 1) NOT NULL ,
[isgroup] [bit] NOT NULL,
[isdisabled] [bit] NOT NULL
)
go

-- create group membership table
CREATE TABLE [groupmembers] (
[groupid] [int] NOT NULL ,
[accountid] [int] NOT NULL
)
go

-- create access table
CREATE TABLE [access] (
[accessid] [int] NOT NULL ,
[accountid] [int] NOT NULL
)
go

-- now create the function
-- it returns the users accountid as well as the accountid
-- of any groups the user belongs to
CREATE FUNCTION fMyFunction(@accountid as int)
RETURNS @t table (accountid int)
AS
BEGIN
DECLARE @isgroup bit
SET @isgroup = 0

SELECT @isgroup = isgroup
FROM account
WHERE
accountid = @accountid
AND isdisabled = 0

-- return account (only if not a group)
INSERT @t
SELECT @accountid
FROM account
WHERE accountid = @accountid AND isgroup = 0

-- return groups that (non-group) account belongs to
INSERT @t
SELECT groupid
FROM groupmembers
WHERE accountid = @accountid

RETURN
END
go

-- the following code produces the error instead
-- of a result
select *
from access
where accountid in (SELECT accountid FROM dbo.fMyFunction(access.accountid))
go

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-01 : 19:04:24

[code]
--A function can only take constants or variables that evaluate to a single value as arguments.
--You can call a table function like this: (which obviously doesn't serve you purpose)

Select a.*
from Access a
JOIN dbo.fMyfunction(1) b
on a.AccountID = b.AccountID

--But a better solution in this case is just to build the function logic into the statement:
--(this statement is probably wrong, but you get the idea)
--Or you could even make this whole statement the function
select access.*
from access
JOIN Account
ON access.accountid = account.accountid
Where account.isdisabled = 0
AND isgroup = 0
UNION ALL
select a.*
from access a
JOIN Account b
ON a.accountid = b.accountid
AND isgroup = 1
JOIN groupmembers c
ON a.accountid = c.accountid
[/code]

Be One with the Optimizer
TG

gdeconto
Posting Yak Master

107 Posts

Posted - 2005-03-01 : 19:15:42

??.

isnt fMyFunction only taking a single variable that evaluates to a single value??

ie shouldnt I be able to do something like:

select accessid, fMyFunction(accountid) as foo
from [access]

since this is essentially the same thing as what I was trying to do below??

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-01 : 19:24:14

No, your second example (which would not be a table function they way you're calling it) would evaluate the function one time for every record returned (row by row). Your original post tried to implement a function which IS a table function. Do you realize there are 2 very different "flavors" of UDFs?

Sorry, I know I'm not being very articulate, it's past my dinner time. Let me go eat while you review functions in Books on line. And I'll try again if you're still having problems.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-01 : 19:57:30

For a function that returns a single value like this:
use pubs
create function dbo.f1(@date datetime)
returns varchar(10)
as
begin return convert(varchar(10), @date, 101) end

you can pass a column as an argument like this:
Select dbo.f1(ord_date) as orderDate from sales

But for a table function:

create function dbo.f2()
returns @tb table (ord_date varchar(10))
as
begin insert @tb (ord_date) Select convert(varchar(10), ord_date, 101) from sales return End

you can't pass a column from a joined table as an argument. But you can JOIN the function to a table on one of the columns in the table returned by the function. (just like it was a table)

Select a.Ord_date, b.Ord_date
from dbo.f2() a
JOIN sales b ON a.ord_Date = convert(varchar(10), b.ord_Date, 101)

Be One with the Optimizer
TG

gdeconto
Posting Yak Master

107 Posts

Posted - 2005-03-02 : 19:38:48

thx guys.

thought there might have been a simple syntax or logic fix so I could run something like

select *
from access
where accountid in (SELECT accountid FROM dbo.fMyFunction(access.accountid))

guess will have to work around this somehow.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources