6

I have a UDF that queries data out of a table. The table, however, needs to be definable as a parameter. For example I can't have:

Select * From [dbo].[TableA]

I need something like:

Select * From [dbo].[@TableName]

The above line doesn't work, and also the UDF prohibits me from setting the query as a string and calling exec(). I can do this in a procedure, but I can't call the procedure from the UDF either.

Does anyone know how I can accomplish this within the UDF without having some kind of massive switch statement?

DaveK
  • 4,509
  • 3
  • 33
  • 33
  • I was trying to do that exact thing the other day with no luck. I hope someone knows! – Gabe Jun 12 '09 at 19:34

6 Answers6

21
SET @SQL = 'SELECT * FROM ' + @table
EXEC (@SQL)  -- parentheses are required
johnnycrash
  • 5,184
  • 5
  • 34
  • 58
  • You may have gotten a negative vote because this leaves you open to a SQL Injection attack depending on how you use this query. EDIT: I just noticed that DaveK said he can't do this in the question - That's probably where your downvote came from. – Tim Hutchison Dec 09 '15 at 14:43
8

This can't be done, dynamic SQL is not supported in functions.

See this SO question: Executing dynamic SQL in a SQLServer 2005 function

Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    This answer is correct when using normal UDFs (which is what I asked about). I was able to solve my problem with a CLR UDF written in C#. – DaveK Jun 15 '09 at 13:33
5

You can UNION ALL your tables and include the table name as a column, then specify the table name as a predicate over this. If you check the query plan for this example you see that t2 is not touched

create table t1 (i int)
create table t2 (i int)

insert t1 values(1)
insert t1 values(2)
insert t1 values(3)
insert t2 values(4)
insert t2 values(5)
insert t2 values(6)


;with tableSet as (
    select i, 't1' as tableName from t1
    union all select i, 't2' as tableName from t2
)
select i from tableSet where tableName = 't1'
ahains
  • 1,912
  • 12
  • 10
  • +1 the question excludes a "massive switch statement" approach tho – Andomar Jun 12 '09 at 20:48
  • @Andomar, I thought this was a little less "switchy" than a bunch of if/else blocks. E.g. this set of tables to UNION ALL could be wrapped into a view if you had many functions that needed the functionality, which you couldn't do with if/else. Point taken though ;) – ahains Jun 12 '09 at 20:55
3

You can write a udf clr that can do dynamic sql. I've had to implement this before. It's pretty slick.

Justin Balvanz
  • 1,186
  • 3
  • 12
  • 19
  • Right, but that would be an extended CLR function, not a UDF? – Andomar Jun 12 '09 at 19:50
  • Can it be used in a query the way a UDF can? – DaveK Jun 12 '09 at 19:57
  • Yeah, but your DBA has to agree to enable CLR support, and you have to deploy them as a compiled .NET assmebly http://msdn.microsoft.com/en-us/library/ms254498(VS.80).aspx – Andomar Jun 12 '09 at 20:06
  • This solution worked great. I was able to write a table-value CLR UDF in C#, with the sql query being generated dynamically. My regular UDF now calls the CLR UDF and assigns the returned values to parameters. I still have to do some scalability testing, but so far so good. – DaveK Jun 15 '09 at 13:32
0

You cannot do it. What problem are you solving, there might be other solutions.

A-K
  • 16,804
  • 8
  • 54
  • 74
0

If you would give more details about what underlying problem you are trying to solve, we might have better answers. One solution is to code generate the UDFs on a per-table basis. Another is to use dynamic SQL from an SP. What's right for your situation is hard to say.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265