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 cant 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 cant update DB state, cant 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.

 

[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 isnt really possible with a UDF - there may be some convoluted way to make this happen but it probably isnt worth the effort.

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