Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: 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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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 it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    78
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pritha
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •