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)
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
select @rv = datepart(year, @when)
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.
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.
CREATE FUNCTION getFiscalYear (@when datetime)
DECLARE @rv int
IF datepart(month, @when) >= 10
SELECT @rv = datepart(year, @when) + 1
SELECT @rv = datepart(year, @when)