davidcahan
asked on
Pass Chinese Characters into Dynamic SQL Stored Procedure
I'm using dynamic sql to execute an update statement via a stored procedure. I have my exec statement set to NVarChar(4000). I have my input parameters for the fields that need to be foreign characters set to NVarChar(). I have my DB datatypes set to NVarChar for those columns. Yet when i pass in chinese characters they end up as ????????? in my DB.
I DO NOT have the same issue with my Insert stored procedure which is not dynamic. That inserts the foreign characters just fine.
I've attached my code. I really really want to get this to work as dynamic sql.
I DO NOT have the same issue with my Insert stored procedure which is not dynamic. That inserts the foreign characters just fine.
I've attached my code. I really really want to get this to work as dynamic sql.
ALTER PROCEDURE [dbo].[UpdateMember]
(
--input parameters
@MemberID int,
@MemberNumber varchar(50),
@Hotel varchar(50),
@FirstName nvarchar(255),
@LastName nvarchar(255),
@DateofBirth DateTime,
@Spouse varchar(100),
@NoOfChildren char(2),
@Address1 nvarchar(255),
@Address2 nvarchar(255),
@City nvarchar(255),
@State nvarchar(255),
@Country nvarchar(255),
@Zip nvarchar(255),
@HomePhone varchar(12),
@AlternatePhone varchar(12),
@Fax char(10),
@Email varchar(50),
@Pwd varchar(50),
@LanguageCode varchar(20) = 'EN',
@MemberTypeCode varchar(50) = 'CL'
)
AS
/* SET NOCOUNT ON */
-------------------------------------------------------------
-- this has to be NVarChar to support foreign characters
-------------------------------------------------------------
Declare @SQL NVarChar(4000)
Set @SQL = '
update dbo.Members
set FirstName = upper( ''' + @FirstName + '''),
MemberNumber = ''' + @MemberNumber + ''',
hotel= ''' + @hotel + ''',
LastName = upper( ''' + @LastName + '''),
DateOfBirth = ''' + Convert(Varchar(100),@DateOfBirth) + ''',
Spouse = upper(''' + @Spouse + '''),
NoOfChildren = ''' + @NoOfChildren + ''',
Address1 = upper(''' + @Address1 + '''),
Address2 = upper(''' + @Address2 + '''),
City = upper(''' + @City + '''),
State = upper(''' + @State + '''),
Country = upper(''' + @Country + '''),
Zip = ''' + @Zip + ''',
HomePhone = ''' + @HomePhone + ''',
AlternatePhone = ''' + @AlternatePhone + ''',
Fax = ''' + @Fax + ''',
Email = ''' + @Email + ''',
Pwd = ''' + @Pwd +''',
LanguageTypeID = (Select LanguageTypeID from dbo.LanguageTypes where Code = ''' + @LanguageCode + '''),
MemberTypeID = (Select MemberTypeID from dbo.MemberTypes where Code =''' + @MemberTypeCode + ''') '
If @MemberID <> 0
Begin
Set @SQL = @SQL + ' where MemberID = ' + Convert(varchar(100),@MemberID)
End
Else
Begin
Set @SQL = @SQL + ' where MemberNumber = ''' + @MemberNumber + ''' '
End
EXEC (@SQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it worked absolutely PERFECTLY once i used sp_executesql. even though the syntax is a bit more verbose, in many ways it's a bunch easier. no more having ot remember to use two or three sets of single apostrophes.
I'm wondering though: normally when i create dynamic sql, i ofthen use the print statement to debug the sql. I will pass in all the values for the parameters but instead of execute i do print. then i copy and paste that into a new query window and debug from there. how would i accomplish that sort of debugging using sp_executesql?
I'm wondering though: normally when i create dynamic sql, i ofthen use the print statement to debug the sql. I will pass in all the values for the parameters but instead of execute i do print. then i copy and paste that into a new query window and debug from there. how would i accomplish that sort of debugging using sp_executesql?
Glad to help. Is this marked as answered now then?
SET @SQL = N'This is Unicode'
SET @SQL = 'This is NOT Unicde'
Even though @SQL is declared as Unicode, it will be doing all the concatenation without Unicode, and then converting it at the end. Try putting N in front of all your strings (like in the above example) to see if that solves it.
Rob