March 18, 2009 at 3:30 pm
Hi,
I have an implementation question. I have a table that contains a list of user defined functions. I am trying to write a query to look at that table and execute the UDFs one by one and give a single unified result set. All the UDFs return the same schema.
(This is how the design is currently and I can't change it. Orders from above :-))
Example: Let's say I have a small table with 3 rows like this.
1 zzz_function1
2 zzz_function2
3 zzz_function3
and all the functions return the same column information
id, code, description. I need the result of the execution of all the 3 UDFs in a single result set.
Do I have to use dynamic queries? I just want to know the best way to do this.
Thanks.
March 18, 2009 at 3:51 pm
Hi
Here a little example:
USE tempdb
IF (OBJECT_ID('dbo.ufn_test1') IS NOT NULL)
DROP FUNCTION dbo.ufn_test1
GO
CREATE FUNCTION dbo.ufn_test1 (@id INT)
RETURNS TABLE
AS
RETURN
(
SELECT column_id, name
FROM sys.columns
WHERE column_id = @id
)
GO
IF (OBJECT_ID('dbo.ufn_test2') IS NOT NULL)
DROP FUNCTION dbo.ufn_test2
GO
CREATE FUNCTION dbo.ufn_test2 (@id INT)
RETURNS TABLE
AS
RETURN
(
SELECT column_id, name
FROM sys.columns
WHERE column_id = @id
)
GO
DECLARE @cmds TABLE (id INT, stmt NVARCHAR(100))
INSERT INTO @cmds VALUES (1, 'dbo.ufn_test1(1)')
INSERT INTO @cmds VALUES (2, 'dbo.ufn_test2(2)')
DECLARE @results TABLE (colunn_id INT, name SYSNAME)
DECLARE @id INT
DECLARE @stmt NVARCHAR(100)
WHILE EXISTS (SELECT TOP(1) * FROM @cmds)
BEGIN
SELECT TOP(1) @id = id, @stmt = stmt FROM @cmds ORDER BY id
SET @stmt = N'SELECT * FROM ' + @stmt
INSERT INTO @results
EXECUTE sys.sp_executesql @stmt
DELETE FROM @cmds WHERE id = @id
END
SELECT * FROM @results
Greets
Flo
March 18, 2009 at 4:26 pm
Wow. Thank you so much for the prompt reply.
I was wondering if there is any easier way in the form of a built-in function that would do the same which I am unaware of. Looks like I HAVE to use dynamic queries.
Anyways, I have modified the while loop part of your query. Let me know if there is any other better way to do this.
USE tempdb
IF (OBJECT_ID('dbo.ufn_test1') IS NOT NULL)
DROP FUNCTION dbo.ufn_test1
GO
CREATE FUNCTION dbo.ufn_test1 ()
RETURNS TABLE
AS
RETURN
(
SELECT 'StudentName' Code, 'Student Name' Description
UNION ALL
SELECT 'Rank' Code, 'Rank' Description
UNION ALL
SELECT 'Score' Code, 'Score' Description
)
GO
IF (OBJECT_ID('dbo.ufn_test2') IS NOT NULL)
DROP FUNCTION dbo.ufn_test2
GO
CREATE FUNCTION dbo.ufn_test2 ()
RETURNS TABLE
AS
RETURN
(
SELECT 'ASC' Code, 'Ascending' Description
UNION ALL
SELECT 'DESC' Code, 'Descending' Description
)
GO
DECLARE @cmds TABLE (id INT, stmt NVARCHAR(100))
INSERT INTO @cmds VALUES (1, 'dbo.ufn_test1()')
INSERT INTO @cmds VALUES (2, 'dbo.ufn_test2()')
DECLARE @stmts NVARCHAR(MAX)
--get all statements to execute at once
SELECT @stmts = ISNULL(@stmts,'') + ' UNION ALL SELECT * FROM ' + stmt from @cmds
--remove the first union all
select @stmts = STUFF(@stmts,1,11,'')
--execute the statements
EXECUTE sys.sp_executesql @stmts
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply