NEW to Enterprise Manager - how to debug?

[AlexmacG]

Apologies for such a basic question but I'm right at the bottom of the learnuing curve...

If I creat a user function to return a varchar representing thre financial year (eg. "2006/2007") from a passed in parameter (date column from a record) how can I debug to find where the function is failing. The particular function equivalent is fine in VB but does not work in SQL. copy of code attached for amusement!

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER function FiscalYear(@Indate as datetime)
Returns varchar
as
begin
declare @YearNo as integer, @MonthNo as integer, @Year1 as integer, @Year2 as integer, @FiscalYear as varchar

begin
set @YearNo = Year(@Indate)
set @MonthNo = Month(@Indate)

if @MonthNo <= '3'
begin set @Year1 = @YearNo - '1'
end
else begin set @Year1 = @YearNo
end
set @Year2 = @Year1 + '1'
set @FiscalYear = LTrim(Str(@Year1)) + '/' + LTrim(Str(@Year2))
end
return (@FiscalYear)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

result returns "2" for curent date (07 sept 2006)

Ta in advance, Alex

 

[gmmastros]

You're in luck. Since you are just starting to learn, you can avoid bad habits.

One particularly bad habit I see is when you use a varchar data type without specifying the length. There are 2 varchar's in your code. Change them both to varchar(20)



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Try declaring @fiscalyear as varchar(9)

It seems to currently be only returning the first character of 2006/2007.

This is what I used to test, I didnt' want to go through the hassle of creating a function:

declare @YearNo integer, @MonthNo integer, @Year1 integer, @Year2 integer, @FiscalYear varchar (9), @indate datetime

set @Indate = getdate()

begin
set @YearNo = Year(@Indate)       
set @MonthNo = Month(@Indate)
    
if @MonthNo <= 3 
    begin set @Year1 = @YearNo - 1 
end    
    else begin set @Year1 = @YearNo
end
    set @Year2 = @Year1 + 1
    set @FiscalYear = LTrim(Str(@Year1)) + '/' + LTrim(Str(@Year2))
end    
select (@FiscalYear)

This will return the desired result (2006/2007), and I don't see it causing any problems when putting this change to your original function.

Hope this Helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

Of course, change your 'returns' to varchar(number > 9) as well. Shouldn't have missed that, thanks George!


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[SQLBill]

To be more specific about what the others have already pointed out:

VARCHAR = VARCHAR(1) --one character
VARCHAR = n characters (ie VARCHAR(10) = ten characters)

The only time this 'rule' fails is with CONVERT and CAST, then VARCHAR defaults to VARCHAR(30), but only with CONVERT and CAST.

-SQLBill

Posting advice: FAQ481-4875

 

[AlexmacG]

Thanks guys, sensible suggestions, thats fixed it. Lack of experience assunmed varchar is like a variable length string in VB/VBA but obviously not a a similar function returning just two characters had been OK, but thats now been explicitly declared.

Are there any good debugging tools out there so that we can step through code and set up breakpoints and watches on variables?

Alex

 

[SQLBill]

Query Analyzer will let you parse the script and tell you where it THINKS the error is. For Stored Procedures there is a debugging tool (built-in to QA) that will let you step through the Stored Procedure. But nothing like that for functions (that I know of).

-SQLBill

Posting advice: FAQ481-4875

 

[AlexmacG]

Thanks all,

Pity about the lack of debugging for functions but your advice has worked.