Link to home
Start Free TrialLog in
Avatar of emi_sastra
emi_sastra

asked on

Pass string builder with line feed to Stored Procedure

Hi All,

I pass a string from string builder to a Stored Procedure. The contents is :
 'RUKO HARCO MANGGA DUA PLAZA" & vbCrLf & "BLOK H NO. 21-21A" & vbCrLf & "JAKARTA PUSAT DKI JAKARTA" & vbCrLf & "TELP. : 021-22393825" & vbCrLf'

I try below :

DECLARE @Address VARCHAR(MAX) = 'RUKO HARCO MANGGA DUA PLAZA" & vbCrLf & "BLOK H NO. 21-21A" & vbCrLf & "JAKARTA PUSAT DKI JAKARTA" & vbCrLf & "TELP. : 021-22393825" & vbCrLf'

SELECT
@Address AS ColOriginal
 

SET @Address = REPLACE(@Address, '"', '''') 

SELECT
@Address AS ColOriginal01
 

SET @Address = REPLACE(@Address, '& vbCrLf &' , '+ CHAR(13) + ') 
SET @Address = REPLACE(@Address, '& vbCrLf' , '')
SET @Address = '''' +  @Address 

SELECT
@Address AS ColOriginal02

DECLARE @NewAddress VARCHAR(MAX) = 'RUKO HARCO MANGGA DUA PLAZA' + CHAR(13) +  'BLOK H NO. 21-21A' + CHAR(13) +  'JAKARTA PUSAT DKI JAKARTA' + CHAR(13) +  'TELP. : 021-22393825' 

SELECT
@NewAddress AS ColOriginal03
 

Open in new window


I expect ColOriginal02 should have the same result as ColOriginal03, but it does not.

What should I do ?

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_25505
Member_2_25505

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of emi_sastra
emi_sastra

ASKER

Hi Randy,

It works.

Thank you very much for your help.
Glad I could help.