SQL Server Performance

why debugging showing LEN(@nm) > 0 when @nm =''

Discussion in 'General Developer Questions' started by vladimir_sim, Jan 23, 2006.

  1. vladimir_sim New Member


    Below is a simple SP created to show my issue.
    If I run the SP, it is working correctly.

    However, when I debug it in QA, it will print 'Length >0' when I pass '' as input parameters. Why is QA having such a issue which confused me.

    Anyone got any ideal why QA is having this issue. I am using SQL2k
    with SP4

    create procedure dbo.a_123
    @i_name varchar(50)
    as begin

    print 'Length > 0'
    print 'Length = 0'

  2. benwilson New Member

    I tried it on my server, and i get Length = 0 when i pass '' , and even if i pass ' '

    I'm not sure why you are getting that error!

    'I reject your reality and substitute my own' - Adam Savage
  3. Madhivanan Moderator

    Check what it returns

    declare @i_name varchar(50)
    set @i_name =''
    select len(@i_name) as i_name_Len


    Failing to plan is Planning to fail
  4. ranjitjain New Member

    Even i'm getting the same as Ben geting.
    Really Strange.......
  5. Madhivanan Moderator

    No It shouldnt be. Did you use char or varchar datatype?


    Failing to plan is Planning to fail
  6. vladimir_sim New Member


    You must create a similar SP and then DEBUG in QA
    it to see this strange issue.

    You don't get to see it if you are declaring the
    variable and testing the LEN() function alone.
    If you test the below code as given by Madhivanan,
    you don't get to see it

    declare @i_name varchar(50)
    set @i_name =''
    select len(@i_name) as i_name_Len
  7. Twan New Member

    In the QA debugger there is no way to set the parameter to '' without modifying the parameter after starting to execute it. Typing '' makes the parameter '''', typing nothing will give an error, typing a single space will make the parameter ' '

  8. vladimir_sim New Member

    Hi Twan,

    Thank for your advise and comment. I pass '' as parameter in the debugger
    which my parameter as '''' and inside the debugger, I will get my parameter
    lenght as 2.

    I thought that I should be passing in '' as my other tool who capture
    my SP show that, similar to

    exec a_123 ''

    So, I thought that I should be passing '' as parameter too.

    Therefore, what should be the correct way to pass in my parameter in the debugger ?
    Or should my SP be using null as the default parameter insteads of passing '',
    please advise.

  9. Twan New Member

    Hi ya,

    If you need to debug the proc as it is then you have to set the parameter to whatever you like first and then when you step onto the first line of the proc, change it to be the empty string '' This seems to be a shortcoming of the way the debugger is implemented in qa

    You could use the default parameter as a work around, but this does change the proc slightly in that it now has an optional parameter rather than a mandatory one, also be careful since len( null ) is null as far as I know which is not = 0 or <> 0


Share This Page