User-defined Function with Dynamically-build Query

Question

User-defined Function with Dynamically-build Query

suparurks

SSC Journeyman

Points: 92
More actions
December 24, 2008 at 4:02 am

#129491

Anyone knows how we can put dynamically-build query (using EXECUTE or sp_executesql) inside user-defined function.
My problem is that I need a table output to join with other tables which seems that user-defined function is the best strategy. However, the query that produces the table output is also dynamically built.
Then, I found out that sp_executesql or EXECUTE cannot be put inside user-defined function. My best strategy turns out to be impossible (in my knowledge).
Is there really impossible? Or there is another way around? Thanks for any responses.
Regards,
Suparurk S

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Depending on what you are actually trying to do, you may be able to accomplish it with a CLR procedure.

You may want to post some information about what you are really trying to accomplish. Once you have gotten to the point in a design where you need to join dynamically generated query output tables, you are very likely to have strayed from the path of good design (don't take that personally, everyone has strayed from that path a few times). It may be possible to do some design re-work and solve the problem in a much more SQL Server friendly manner.

suparurks SSC Journeyman Points: 92 More actions · Answer 2

Thank you for your reply. My task is that I have a master data (says TableA) with 4x,xxx records. Users need to map sets of data into groups for reporting purpose such as:

Code 00000-10000, 20000-30000 except 25000-25999 = Group 1

Code 10001-19999 = Group 2

The mappings may change each month.

I decided to create a table with 4 columns (TableB) --Group, BeginCode, EndCode, IsExcluded--keeping the mapping data so that users do not need to map all 4x,xxx records one by one.

Then I generate where clause from TableB for each Group and use this where clause to dynamically map Group to TableA data into a temporary table. So that I can use this temporary table to join with transaction data and get result into Groups.

Actually, it already seems too complicated :unsure:. The requirement is that users can change Groups and their Groups' code ranges. They can not map or write SQL queries to map TableA with Groups by themselves (too many rows). Any suggestions are welcome.

Regards,

Suparurk S

jon-474332 SSC Eights! Points: 839 More actions · Answer 3

doesn't seem to me that you'll need a UDF seems like a SP with a cursor would solve your problem. If performance is a concern then you should adopt what Michael suggested, using a CLR 😉

cheers 🙂

jon

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 4

Cursors are evil, aren't they? 😎

Here's a solution that seems to work without cursors and CLR.

create table TableA(itemid int identity(1, 1), itemname sysname)

go

set rowcount 500

--populate tableA. if you need more rows, change rowcount and uncomment references to tables b and c

-- in the query

insert TableA (itemname)

select 'test item ' + convert(varchar, row_number() over (order by a.object_id))

from sys.all_objects a --, sys.all_objects b--, sys.objects c

set rowcount 0

go

create table TableB(groupid int, minrange int, maxrange int, isexcluded bit)

go

--populate tableB. add a few ranges included and excluded