Post reply

Passing a datetime parameter while debugging a stored procedure

  • October 11, 2004 at 9:42 am

    #59623

    Hello.

    I'm stuck trying to do something that should be quite simple.

    I have a simple stored procedure (see below) that accepts a SMALLDATETIME variable. I'm trying to debug it but when I try to specify a value in the Debug Procedure, I keep getting a message saying ...

    [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

    I have tried all sorts of value combinations including '01-DEC-2004', 01-DEC-2004, 01122004, etc. I've even tried DATETIME rather than SMALLDATETIME.

    I can't seem to find any help on this!

    Here is my stored procedure ...

    CREATE  PROCEDURE TESTDATE (@MYDATE SMALLDATETIME) AS

    SELECT @MYDATE

    GO

    Thanks.

    - Mike

  • October 11, 2004 at 10:22 am

    #526208

    I just ran it like this:

    EXEC testdate '2004-10-11'



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • October 11, 2004 at 10:30 am

    #526210

    Thank you AJ but I'm actually trying to debug the stored procedure in Query Analyzer rather than execute it. If you right click on a stored procedure in Query Analyzer, the bottom menu option is Debug ...

    Note that the real procedure I'm trying to debug is much more complex. I've just pared it down to this simple procedure to show the problem I'm having with the debug parameter value.

    - Mike

  • October 11, 2004 at 12:13 pm

    #526229

    I was just able to run it in debug with 2002-01-01 with no tick marks.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • October 11, 2004 at 12:27 pm

    #526232

    Thanks Kathi. That worked for me also.

    Strange how the order of the date value is so important. I would have thought 01-JAN-2004 or 2004-01-01 would work too.

  • October 11, 2004 at 12:38 pm

    #526235

    In my last sentence, I actually meant to say ' ... or 01-01-2004' would work too'.

    - Mike

     

  • October 11, 2004 at 5:43 pm

    #526269

    Mike,

    I know now what you are talking about and I dont use the debugger in QA.  It just feels wrong (don't ask).  I generally write stored-procedures as TSQL code in QA 1st and then make a stored-procedure out of them.  If they need further debugging I put SELECT statements and/or writes to temp tables to review the outputs for review.

    My background for debugger is VB6 and .NET where you need the debugger.  In SQL it feels like cheating to me..  I don't really use the GUI for SQL development.  Granted the debugger is a nice tool I just like doing it the hard way.  Guess Im an old dog who doesnt like new tricks



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply