Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: stored procedure in place of UDF?

    thanks for reading.

    i'm wondering if i can use a stored procedure in place of a UDF in the case where i want a return value based on a simple calculation involving the input parameter.

    i'd like to use this inline in a query somewhere else. that's why the UDF.

    the UDF would be something like this ...

    create function getFiscalYear (@when datetime)
    returns int
    as
    BEGIN
    declare @rv int

    -- months of Oct, Nov, Dec are rolled into following year
    if datepart(month, @when) >= 10
    select @rv = datepart(year, @when) + 1

    -- whereas all other months stay in this year
    else
    select @rv = datepart(year, @when)
    end

    END

    so, the only reason i'm not using this UDF (and haven't tested it either) is because i can't find (or can't remember how ) to add a UDF to my database. when i run this code in Query Analyzer i get an error on the keyword "function". but that's another question altogether.

    thanks in advance. (a) for helping with a stored procedure that does the equivalent or (b) nudging me in the right direction towards getting UDFs to work in my SQL 2000 install.

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Yes you can use a stored procedure. However, it looks like what you're doing is better suited as a function. It looks like you have a return missing too.

    Here's a sample UDF:

    create function dbo.udf_nasd_status
    (@nasd_id int,
    @effective_date datetime,
    @term_date datetime,
    @getdate datetime)
    RETURNS char(10)
    AS
    BEGIN

    DECLARE @nasd_status char(10)

    SELECT
    @nasd_status = CASE
    WHEN (@term_date < @getdate) Then 'Terminated'
    WHEN (@effective_date < @getdate and (@term_date > @getdate or @term_date Is Null)) Then 'Active'
    ELSE 'Pending'
    END

    RETURN (@nasd_status)
    END
    go
    Last edited by peterlemonjello; 01-07-04 at 12:13.

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    Originally posted by peterlemonjello
    Yes you can use a stored procedure. However, it looks like what you're doing is better suited as a function. It looks like you have a return missing too.
    thanks peter. it would be great if i could remember how to add a UDF. i'm stuck here because i don't see a "container" for UDFs in my database tree. shouldn't it be near Rules, User Defined Data types, etc?

    am i lost? maybe i need more lemon jello in my diet.

    thanks.

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    It's below user defined datatypes in Enterprise Manager and above user defined datatypes in Query Analyzer. (I'm using SQL2K)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your server version is 2000, but what version is your database? Run:

    select name, cmptlevel from master..sysdatabases

    to verify that your database has been upgraded.

    blindman

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...it's malformed...

    maybe you're at 7.0..

    How many registered servers do you have?

    Are opening QA in the correct one?

    Try this...it should compile..it does for me..

    Code:
    CREATE FUNCTION getFiscalYear (@when datetime)
    RETURNS int
    AS
    BEGIN
    	DECLARE @rv int
    	IF datepart(month, @when) >= 10 
    		SELECT @rv = datepart(year, @when) + 1
    	  ELSE 
    		SELECT @rv = datepart(year, @when)
    	RETURN @rv
    END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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