why debugging showing LEN(@nm) > 0 when @nm ='' | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

Hi, 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 if(len(@i_name)>0)
print ‘Length > 0’
else
print ‘Length = 0’ end
GO
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
Check what it returns declare @i_name varchar(50)
set @i_name =”
select len(@i_name) as i_name_Len
Madhivanan Failing to plan is Planning to fail
Even i’m getting the same as Ben geting.
Really Strange…….
No It shouldnt be. Did you use char or varchar datatype? Madhivanan Failing to plan is Planning to fail
Hi, 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
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 ‘ ‘ Cheers
Twan
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.
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 Cheers
Twan

]]>