Calculating values in multiple tables within a UDF in SQL server

Hi,
I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help?

Here's an example of a counter that I'm using to return the number of days.

CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@.date smalldatetime, @.date2 smalldatetime, @.osid int)

RETURNS int AS

BEGIN

return (select count(*) from MarketPulse_0ndqc
where stockosid = @.osid and createdate <= @.date and createdate >=@.date2 )

END

How can I use this same UDF to do the same computation but to SELECT from another table and return that value?You would have to use dynamic SQL, which off the top of my head I am not sure is even allowed in a UDF (perhaps with some limitations), but I am sure it is not a good idea...|||If you are only choosing between a few tables you could pass through another parameter which indicates which table you want to query and then use it to choose which statement will run.
e.g.
if @.parm = 1 then
select ... from tableA
else if @.parm = 2 then
select ... from tableB
...
Or you could use a case statement.

Personally I am rather fond of dynamic SQL. Since I don't have my BOL on this machine I have no idea whether its use is possible in UDFs either.|||I don't know of any way to use dynamic SQL via Transact-SQL within a UDF. You can't use EXECUTE ('dynamic sql') and you can't execute sp_executesql either.

-PatP|||Hi,
I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help?

Here's an example of a counter that I'm using to return the number of days.

CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@.date smalldatetime, @.date2 smalldatetime, @.osid int)

RETURNS int AS

BEGIN

return (select count(*) from MarketPulse_0ndqc
where stockosid = @.osid and createdate <= @.date and createdate >=@.date2 )

END

How can I use this same UDF to do the same computation but to SELECT from another table and return that value?

You know, your example is NOT returning a computed value. It is just returning an aggregation. A subtle point, maybe, but are you sure the UDFs are really appropriate for this task?

Most, (though not all) of the scalar UDFs I have written do not query underlying tables except possibly for configuration values.