use UDF that returns SQL as subquery in select? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

use UDF that returns SQL as subquery in select?

Author

Topic

VillageIdiot
Starting Member

3 Posts

Posted - 2010-04-07 : 19:08:10

Hi, I'm new here and not too familiar with t-sql, hoping to find some help.

Is there any way to use a function return as a subquery in a select statement if the function returns a SQL statement?

I have an existing function in the database that accepts 5 input parameters and creates a very complex SQL SELECT statement.

I'd like to use it in a SELECT INTO that creates another table.

EXAMPLE with a much simpler SQL statement as the return:

create function [dbo].[CreateSQL]
returns varchar(max)
as
begin
return 'select 15 as AverageHours'
end

select (dbo.CreateSQL) as AverageHours into MyTable

This returns 'select 15 as AverageHours'. I need something that returns '15'.

Does anyone have any ideas? Thanks in advance!!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 19:52:23

Does this help?

CREATE TABLE #tmp (x INT)

INSERT INTO #tmp
EXEC('select 15 as AverageHours')

SELECT * FROM #tmp

DROP TABLE #tmp

There are 10 types of people in the world, those that understand binary, and those that don't.

VillageIdiot
Starting Member

3 Posts

Posted - 2010-04-07 : 19:59:33

THIS IS THE BEST FORUM EVER!!!

That works wonderfully.

Thank you very much, DBA!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 00:59:07

quote:
Originally posted by VillageIdiot

THIS IS THE BEST FORUM EVER!!!

That works wonderfully.

Thank you very much, DBA!

Can i ask the need of this reqmnt? Are you storing queries themselves as values in some table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VillageIdiot
Starting Member

3 Posts

Posted - 2010-04-08 : 09:21:06

I have a set of existing functions that accept 5-6 parameters each and assemble dynamic SQL statements. They're date-dependent, among other things, and the outputs are structured differently based on the parameters.

My job is to build a table using the outputs from those functions, and the easiest way is to call them. I'll call them from another function using DBA's code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-08 : 09:33:57

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources