Replaceable Parameters in UDF's

[PrOpHeT]

In a stored procedure to use a replaceable parameter in a query I would build the query as a string, then use execute.

 

This does not seem to work in functions,

 

How could I perform the same query with a variable tablename

 

Like

 

SET @Query = 'COUNT(*) FROM ' + @TableNameVariable

Select @VarNum1 = @Query

 

Does not work because it trys to set the Variable @VarNum1 to a string not the result of the query.

 

Is there another method of producing variable sql queries inside a UDF?

[Mister E]

Use the sp_executesql stored procedure for executing dynamic sql:

 

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q262499

 

You can't execute non-deterministic code or stored procedures in a UDF. If you want to return an integer value through some dynamic sql, just return it from a sproc.

 

Example:

DECLARE @count int

EXEC @count = spGetCount

PRINT @count

[PrOpHeT]

Could it be said that ALL SQL queries in a UDF have to be HARD coded?

 

Is that what I seem to be over looking?

 

So if I want a function that can take the same column name from several different variable named tables and return a value based on that, it can simply not be done?

 

No Way?

 

All I need is a number, not a dataset or table, just simply a number that is the product of a field that exists in more than one table.

 

 

I can determine what those tables are, and process them in a loop, but do nothing with them?

 

 

I just seems to me crazy something as powerfull as MS SQL could explicitly prohibit something that seems so simple.

 

O' well, it is a rant better aimed at MS not here, thank your help.

[PlausiblyDamp]

Certain bits of a UDF can be parametrised however the function as a whole needs to be deterministic (as Mister E mentioned).

This does restrict the functionality of a UDF - it can't update DB state, can't use non-deterministic functions or stored procs etc.

Depending on what you are attempting to do the may be an alternate way, also http://weblogs.sqlteam.com/jeffs/articles/1490.aspx might be worth a read as it does give a method of using non-deterministic functions.

Edited March 4, 2007 by PlausiblyDamp

[PrOpHeT]

Ok, the example uses an SP AND the EXECUTE statement in a function, Why can I not?

 

I simply want to be able to cycle through a cursor and perform a query on each tablename variable fetched from that cursor.

 

I.E.

 

I Run a query that returns a list of all the tables with relations to a main table.

 

Then run a query in a loop using the tablenames from that list.

Same query for each, just substituting the Table Name with a variable from the list.

 

Can this be done? It has been the general concensus elsewhere it can not.

[PlausiblyDamp]

They use an extended procedure, not a stored procedure. What you are trying to do isn't really possible with a UDF - there may be some convoluted way to make this happen but it probably isn't worth the effort.

 

You are probably better off just using a stored proc to achieve your aim with this.