Odd behavior with 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 2005 Forums

Transact-SQL (2005)

Odd behavior with UDF

Author

Topic

woogychuck
Starting Member

2 Posts

Posted - 2007-08-31 : 10:30:29

I'm hoping this is the right section to post this in.

I'm having some issues with returning data correctly from a UDF. My database has a folder table where each folder has an id and a column with the id of it's parent folder. I've written the following UDF to recursively retrieve all of the folders and their children from a comma seperated list.

ALTER FUNCTION [dbo].[GetChildFolders](@FolderIds nvarchar)

RETURNS 
@ChildFolders TABLE 
(
	sid_folder_id int NOT NULL,
	dim_folder_parentid int NOT NULL
)
AS
BEGIN
	
	WITH myFolders(sid_folder_id, dim_folder_parentid)
	AS(
		SELECT	sid_folder_id, dim_folder_parentid FROM dbo.Split(@FolderIds,',') 
			INNER JOIN dbo.Folder on sid_folder_id = data
		UNION ALL
		SELECT tpc.sid_folder_id, tpc.dim_folder_parentid
			FROM dbo.folder tpc WITH(NOLOCK)
			  INNER JOIN myFolders
			 ON myFolders.sid_folder_id = tpc.dim_folder_parentid
	)

	INSERT INTO @ChildFolders SELECT * FROM myFolders;
	
	RETURN
END

If I run this as a query, it returns correctly. However in the UDF it only returns 1 row with bad data.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-31 : 12:20:04

what does this have to do with .net CLR inside sql server??
moved.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

woogychuck
Starting Member

2 Posts

Posted - 2007-08-31 : 12:52:17

Wow, such friendly admins in this forum.

Shouldn't that have included the phrase "Stoopid N00b!!".

Don't worry I figured it out on my own, but thanks for the warm welcome to your forums.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-31 : 12:54:41

lol. you almost had me there

welcome to the forums!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 15:14:45

Another dissatisfied customer.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 04:46:56

You're more likely to get an answer if you post a complete worked example, including DDL to create a temporary table and populate it with some sample data, and then show the output you expect. Folk can then just debug that for you, otherwise we all have to do all that work ourselves, individually.

That's assuming that some bright spark can't just see some simple mistake that you have made. I can't, although I'm not a very bright spark anymore!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources