Error checking simple parameter passing in problem, please advise.

  • 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

  • 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.

  • 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

  • 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

  • >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

  • 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

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

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