UDF Question - 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

Transact-SQL (2000)

UDF Question

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-01-17 : 23:06:27

Let me preface this by telling you that I have never written a UDF nor have I have ever used one. But my boss uses them quite a bit for reporting off of our software. He is stumped on something and I am not any help. Normally I can search this forum to find an answer if I am really stumped, but I dont have a clue where to start.

He has written this UDF that works:
CREATE FUNCTION DBO.ChildBdayList (@entityID AS INT)
RETURNS varchar(1000) AS
BEGIN
declare @Childlist nvarchar(1000)
set @Childlist =''
select @Childlist = @Childlist + DP.FirstName + ' - ' + +Dp.datevalue1+ ', '

FROM CTCPeople.dbo.ChildBirthdayList DP
WHERE (DP.ParententityID = @EntityID)
Order By Dp.NumberSuffix

--trim off extra comma at end
if @childlist <> '' set @Childlist = left(@Childlist, len(@Childlist) - 1)

return @ChildList
END

He has written this UDF that is very similar that does NOT work. The main difference in this one and all the others is that this one has two parameters being passed. But from what I read and other examples I have seen on this site, the number does not matter. He says that if he takes one of the parameters out and sets the value, then he gets the data he needs.

CREATE FUNCTION DBO.MemUserField (@entityID AS INT,@Descrip AS nvarchar )
RETURNS varchar(1000) AS
BEGIN
declare @MemUser nvarchar(1000)
set @MemUser =''
select @MemUser = @MemUser + UD.UserValue + ', '

FROM CTCPeople.dbo.UserDefinedData UD
WHERE (UD.entityID = @entityID) AND(UD.UserDesc=@Descrip )
Order By UD.entityID

--trim off extra comma at end
if @MemUser <> '' set @MemUser= left(@MemUser, len(@MemUser) - 1)

return @MemUser

END

Anyway, I know this is not a lot of info for you, but unfortunately it is all I have. I dont know if someone can see this and see something wrong or not, but I thought I would take a chance. You have all made me look good more then once, so it is a good chance to take. :)

Thanks,
JAdauto

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-17 : 23:13:33

Define the size of @Descrip

CREATE FUNCTION DBO.MemUserField (@entityID AS INT,@Descrip AS nvarchar(100) )

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources