Store Procedures with output parameters

[RPredrag]

Ok here is question, I have created procedure to check for ~ character in customers name and if there is one than take it out and flip last part of the name to the front. Should look like this, if name is in table like 'Ruzic~Predrag' procedure should return 'Predrag Ruzic' (character ~ is insert in table with customer name for searching purposes).

Here is proc:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


Create proc pr_FlipName(
@chCustName as varchar(30),
@chFlipName as varchar(30) output)

As



declare @chFName as varchar(30)
declare @chLName as varchar(30)
declare @intLen as integer
declare @intPos as integer


set @intLen = Len(@chCustName)
set @intPos = CharIndex('~',@chCustName)

--Searched character found
If @intPos > 0
Begin
set @chLName = Left(@chCustName, @intPos - 1)
set @chFName = Right(@chCustName, @intLen - @intPos)

set @chFlipName = rtrim(ltrim(@chLName)) + ' ' + rtrim(ltrim(@chFName))

End

Else
Begin
set @chFlipName = @chCustName
End

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I'm calling this proc from other proc where name should be return together with other customer info.


Calling Proc.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



/*Proc. calls pr_FlipName and displays results */

create proc pr_CustName(@chCustID as varchar(15))

As

declare @chCust as varchar(30)
declare @chFullName as varchar(30)

--Get customer name from table

select @chCust = (Select Name From Customer where CustId = @chCustID)

/******
Because customer name could contain ~ character and in most cases does,
as part of searching way one of our application use.
So I call pr_FlipName proc.
******/

exec pr_FlipName @chCust, @chFullName output

select @chFullName CustomerName, @chCustID CustNum


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

What happens is that I get just first part of name for example if name is Ruzic~Predrag I would get just Predrag, But if I execute proc. this way

>>>>>>>>>>>>>>>>>>



declare @cFName as varchar(30)
exec pr_FlipName 'Ruzic~Predrag', @cFName output

select @cFName

>>>>>>>>>>>>>

it works fine.

I figure out way around creating Dll in VB and than called it in Sql Server 2000 I am still curios why this does not work. Any ideas.

Thanks a lot

 

[Allumo]

This could be happening cause @chCustName is getting adicional spaces from the field Name:

@chCustName = 'Ruzic~Predrag '

Before setting @intLen, try to remove the spaces from @chCustName using:

@chCustName = Trim(@chCustName)

Hope this helps.