How to create Multi-Statement TVUDF w/ dynamic SQL

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 2008 Forums

Transact-SQL (2008)

How to create Multi-Statement TVUDF w/ dynamic SQL

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2011-10-21 : 15:04:04

I need to create a multi-statement Table-Valued Function that uses dynamic SQL within in. I'm converting it from a stored proc because I want to be able to select from the result set.

I'm posting the code below.

What I can't figure out is how can I actually return the results?

CREATE FUNCTION dbo.GetActiveMDS_IDs()
RETURNS @MasterDataSets TABLE
	(mds_id BIGINT NOT NULL,
	table_name VARCHAR(200) NOT NULL)
AS
BEGIN
	DECLARE @SQL varchar(max);
	SET @SQL = '';

	WITH MasterDataSets (TableName)
	AS 
	(
		SELECT 
		  o.name As TableName
		FROM
		  syscolumns c
			INNER JOIN sysobjects o ON c.id = o.id
		WHERE
		  o.xtype = 'U' AND c.name = 'mds_id'
	)
	SELECT 
		@SQL = @SQL+'SELECT DISTINCT mds_id, '''+TableName+''' as table_name FROM '+TableName+' UNION ALL '
	FROM
		MasterDataSets
	ORDER BY 	
		TableName;

	SET @SQL = SUBSTRING(@SQL, 0, LEN(@SQL)-9);

	--INSERT @MasterDataSets??
	EXEC (@SQL);
	
	RETURN 
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-21 : 15:57:16

http://www.sommarskog.se/dynamic_sql.html#UDF

"This very simple: you cannot use dynamic SQL from used-defined functions written in T-SQL."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-21 : 16:11:37

quote:
Originally posted by Lamprey

http://www.sommarskog.se/dynamic_sql.html#UDF

"This very simple: you cannot use dynamic SQL from used-defined functions written in T-SQL."

And why are you even using a stored procedure when you're passing a table name as a parameter? It completely defeats the purpose.

malachi151
Posting Yak Master

152 Posts

Posted - 2011-10-21 : 16:11:57

quote:
Originally posted by Lamprey

http://www.sommarskog.se/dynamic_sql.html#UDF

"This very simple: you cannot use dynamic SQL from used-defined functions written in T-SQL."

Yeah, I was trying to figure some other way to do it, using a cursor or something, but I can't figure anything out, so I just kept it as a stored proc that dumps the values to a table specified by a parameter.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources