Error checking simple parameter passing in problem, please advise.

Question

Post reply

Error checking simple parameter passing in problem, please advise.

Vladimir Sim

Ten Centuries

Points: 1310
More actions
June 20, 2006 at 10:20 am

#94054

Hi,
I have this weird issue on parameter passing in SP.
I cannot figure out why the parameter when pass in with '',
will give len(@w_nm) = 1. See my example below
1. Declare @nm = ''
print 'Len(@nm) = '+str(len(@nm)) -- return 0
2. create procedure dbo.testing(@nm varchar(10))
as begin
if len(@nm) > 0 -- the len return is 1, why ?
print 'Len = '+str(len(@nm))
else
print 'Empty parameter pass-in'
end
When I do exec testing '', it will return 'Len = 1'
Why is the Sp always returning len of 1 instead of 0. I know that
the best option of empty parameter is to pass by null, but I have to
handle SP written by other.
Please advise/comment why the Len = 1 is return by the sp and what
is the best way to check for the '' parameter ?
Thank you

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

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Are you sure it's passing in '' and not some whitespace characters?

Have a look at this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=288843

for some troubleshooting ideas.

stax68 SSChampion Points: 11711 More actions · Answer 2

If you run EXACTLY this:

drop procedure dbo

.testing

go

create procedure dbo.testing(@nm varchar(10))

as

begin

if

len(@nm) > 0

print

'Len = '+str(len(@nm))

else

print

'Empty parameter pass-in'

end

go

set

ansi_padding off

Declare @nm varchar(10)

set

@nm = ''

exec

dbo.testing @nm

What result do you get?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 3

Hi,

Thank for the feedback/advise.

1. Yes, I am getting the correct result from stax68. My QA when I run it, will pass ''''

to the SP, hence it giving len > 1.

Plus, when I debugging the SP, I will pass '' which end up as '''' in the SP,

hence the len(@nm) > 1 will be trigger.

2. Therefore, what should be the correct parameter to pass in to my SP if I want to

pass ''. I can't change the SP much as it will affect other of my code base.

Thank you very much

stax68 SSChampion Points: 11711 More actions · Answer 4

>when I debug the SP, I pass '' which ends up as '''' in the SP

Can you post the code you use to debug the sp? I think you must be using '' as the content, rather than the definition of the content, of a variable.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 5

Hi.

1. Yes, I am using the '' as my parameter when debugging, hence it is shown as '''' in the debug window, hence len > 1. I should have pass a space by using the spacebar, then it will work.

2. Thank alot for the advise/help