UDF - SQL Server Forums

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

SQL Server Development (2000)

UDF

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-05-11 : 11:19:12

I have a udf...(below)
if I do (Select filesid from dbo.fGetHeirarchyForFile(27)
I get the expected result set.

but if I want to do
Select thisid from thattable a inner join dbo.fGetHeirarchyForFile(thattable.thisfld) as b
on a.thisid = b.thisid

it pukes. can't you pass columns into a udf?

CREATE Function dbo.fGetHeirarchyForFile ( @FilesID bigint)
returns @Heir TABLE(filesid bigint)
AS
begin

declare @separator char(1)
,@heirstr varchar(500)

Set @heirstr = (Select heirarchy from Files where FilesID = 40)
set @separator = '|'

declare @separator_position int
declare @array_value varchar(500)

set @heirstr = Reverse(@heirstr)
while (patindex('%|%' , @heirstr) <> 0)
begin
select @separator_position = patindex('%|%' , @heirstr)
select @array_value = left(@heirstr, @separator_position - 1)

Insert into @Heir(filesid)
Values (Cast(Reverse(@array_value) as bigint))

select @heirstr = stuff(@heirstr, 1, @separator_position, '')
end
RETURN
end

________________________________________________

Bier is a privelege of the working man (or woman).

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-11 : 11:58:42

quote:
Originally posted by Vivaldi

it pukes. can't you pass columns into a udf?

I pass column values to my UDFs all the time.

Could you expand on "pukes" a little?

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-11 : 12:01:21

The problem may be the context. You're using the UDF in an INNER JOIN and passing the joining table's column value as a parameter.

Makes me dizzy trying to think about that.

What are you trying to do?

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-05-11 : 12:02:18

<smile> sure.

if I do...
select PermID from Perms p
inner join dbo.fGetHeirarchyForFile(FileID) x
on p.fileid = x.filesid

i get....'FileID' is not a recognized OPTIMIZER LOCK HINTS option.

if ( add (p.FileID))
I get....Incorrect syntax near '.'.

________________________________________________

Beer is a privilege of the working man.

In the fridge: Edil Pils.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-05-11 : 12:10:11

quote:
Makes me dizzy trying to think about that.

Me too.

I have a psuedo file mgmt system (web app)

when a user requests to see the files/folders in the system,

I need to check the Perms Table for that user.
I have a lineage string stored for each file so I know the current file and its parents. the function splits the lineage string and I thought I could join on that, return the results.

However, each user may or may not have permissions on that file, its container (or any of the parent containers), in addition, the user may have global permissions. To throw in the mix, there are groups who can have the same permissions, but are secondary to the users permissions. (Think of it as window security, everything is inherited unless you give an item more specific permissions.

Anyway, I want to filter files without the view/modify/etc permissions . The simple way is an ugly recursive call in the webpage, but I just can do that.

________________________________________________

Beer is a privilege of the working man.

In the fridge: Edil Pils.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-11 : 12:12:58

Try

SELECT ThisID
  FROM ThatTable 
  WHERE ThisID IN (
    SELECT ThisID
      FROM dbo.fGetHeirarchyForFile(ThatTable.ThisField)
  )

This does what your inner joing is trying to do... it isn't pretty from an execution plan point of view.

The WHERE could be written as

WHERE EXISTS (SELECT 1 FROM dbo.fGetHeirarchyForFile(ThatTable.ThisField) WHERE ThisID=ThatTable.ThisID)

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-05-11 : 12:24:46

Nope, neither work.

I started out with a sub query, then went to the join.

I am guessing there is something about passing in a column that you are joining on.

Outside of "climbing the tree" in a while loop, I am not sure how to insure I am getting the most explicit permissions for a given file and user.

I have thought about a view that builds all the combinations of user/file but that is aweful difficult too.

________________________________________________

Beer is a privilege of the working man.

In the fridge: Edil Pils.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-11 : 13:16:05

Maybe you should give in and build a table like

CREATE TABLE dbo.IGiveUp (
  ThisField INT NOT NULL ,
  ThisID INT NOT NULL )

Next, throw together a loop that populates the table for all values if ThisField using fGetHeirarchyForFile

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-05-11 : 13:32:20

quote:
Originally posted by SamC

Maybe you should give in and build a table like

CREATE TABLE dbo.IGiveUp (
ThisField INT NOT NULL ,
ThisID INT NOT NULL )

Next, throw together a loop that populates the table for all values if ThisField using fGetHeirarchyForFile

thanks for the attempts.
I was only going to use the udf as part of the much bigger problem.
I always have a backway out, just looking for better performance..

________________________________________________

Beer is a privilege of the working man.

In the fridge: Edil Pils.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-11 : 14:41:11

You will get much, much better performace by dropping the UDF and using an efficient JOIN along with proper indexes and a good database design.

(almost) never use a UDF to look up values in other tables; that moves away from set-based, relational database programming into more of a "1 line at a time" cursor mindset.

- Jeff

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-05-11 : 14:47:58

quote:
Originally posted by jsmith8858

You will get much, much better performace by dropping the UDF and using an efficient JOIN along with proper indexes and a good database design.

(almost) never use a UDF to look up values in other tables; that moves away from set-based, relational database programming into more of a "1 line at a time" cursor mindset.

- Jeff

I figured I would lose the performance, but I don't know any other way when dealing with N levels of recursion. I do know, that almost no matter what I do in SQL, it will be faster than N calls to the database from ASP

Its hard to get away from the one at a time mindset, people like me who deal with arrays everyday.....

________________________________________________

Beer is a privilege of the working man.

In the fridge: Edil Pils.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources