Debuging Scalar-Valued Function in Visual Studio with date parameters

[Shannon Broskie]

Hello,

I couldn't find a better newsgroup to put this post in. If there is one,
feel free to respond with that group.

I'm running VS .NET 2003 Ent Arch., tapping into a MS SQL 2000 database.

I am trying to debug a function that I wrote on the SQL Server. The
function has three parameters including two smalldatetime parameters.

CREATE FUNCTION dbo.DaysBetweenByAccrual (@Day1 smalldatetime, @Day2
smalldatetime,
@AccrualType varchar(8))
RETURNS int
....

My issue is when trying to run or step through the function using VS, I
cannot enter (or do not know the proper format) the date parameters in the
parameter input dialog. I've tried entering the following formats and I
also show the resulting value in the locals window:

Date Format Result
mm/dd/yyyy 1/1/1900 12:00:00 AM
mm/dd/yyyy 12:00:00 AM Does not run
'mm/dd/yyyy' Error: @Day1: this input parameter
cannot be converted
#mm/dd/yyyy# Error: @Day1: this input parameter
cannot be converted

The database output is the following: If I placed 01/01/2004 and 02/01/2004
for the dates.

Running dbo."DaysBetweenByAccrual" ( @Day1 = 01/01/2004, @Day2 = 02/01/2004,
@AccrualType = 30/360 ).

I can get around this issue by editing the value of the dates in the locals
window but there's got to be a way to pass these parameters through the
dialog so that it works and not always pass them in as 1/1/1900.

Any suggestions?

Thanks in advance.

 

[Mary Chipman]

My personal recommendation would be that when debugging SQL Server
routines, use the SQL Server client tools, in this case the Query
Analyzer. You can then spend more time actually debugging and less
time struggling with the VS visual tools.

--Mary

 

[Shannon Broskie]

I thank you for your response Mary but I know of no SQL Server client tools
that allow you to debug User Defined Functions. You have to wrap them into
a stored procedure.

By the time I do all of that work, I haven't gained any time above and
beyond the time wasted manually editing the variables in the watch window.

 

[Mary Chipman]

The wrapper stored procedure call is one line -- to call the UDF. An
alternative to the T-SQL debugger is to create your own wrapper
harness in T-SQL in QA that calls your UDF where you can plug in
different parameter variables and then process the output parameters
or result sets by displaying them in the results pane. It's decidedly
low-tech since you're just writing your own script, but it saves time
futzing with the modal dialog windows you get with the debugger. You
can easily add and remove comments to multiple lines of code, so if
you have several test cases in one script, you just comment out the
ones you're not working with at the moment. Believe me, I totally
understand your frustration with the debugging tools -- I've been
there, done that, which is why I'm suggesting this alternative. Use
QA, write your own script/test harness, save, reuse as needed.

--Mary