SQL use Unicode N in a Stored Procedure with variable

Question

I have the following Stored Procedure, Im looking for the correct syntax so I can play the Comments Value in the Comments column with N in front end the value for Unicode I need save Russian Characters Values

So at the moment the comments value is being passed as such

@comments

I want to do

N@comments but not working

ALTER PROCEDURE [dbo].[spInsertContactUs]
(
@title VARCHAR(20) = default,
@forename VARCHAR(100) = default,
@surname VARCHAR(100) = default,
@gender VARCHAR(50) = default,
@address1 VARCHAR(100) = default,
@address2 VARCHAR(100) = default,
@city VARCHAR(50) = default,
@county VARCHAR(50) = default,
@country INT = default,
@zipcode VARCHAR(50) = default,
@email VARCHAR(200) = default,
@comments NVARCHAR(MAX) = default,
@mailinglist BIT = default,
@address3 VARCHAR(100) = default,
@dateOfBirth datetime = default
)
AS
SET NOCOUNT ON

INSERT INTO tblContactUs (
dateAdded,
title,
forename,
surname,
gender,
address1,
address2,
city,
county,
country,
zipcode,
email,
comments,
mailinglist,
address3,
dateOfBirth)
VALUES (
getdate(),
@title,
@forename,
@surname,
@gender,
@address1,
@address2,
@city,
@county,
@country,
@zipcode,
@email,
@comments,
@mailinglist,
@address3,
@dateOfBirth
)

SET NOCOUNT OFF
RETURN

;

As an aside, it's a bad idea to name a stored procedure with "sp***" in SQL Server since the query analyzer ALWAYS searches for a system stored proc if it has that naming convention. — JNK, Aug 13 '10 at 14:46
i have updated my answer. http://stackoverflow.com/questions/1856239/classic-asp-how-to-write-unicode-string-data-in-classic-asp — garik, Aug 13 '10 at 15:06

score 3 · Accepted Answer · edited May 23 '17 at 12:24

3

Use Nvarchar data type in table's fields and stored procs parameters.

ADDED

See this link, maybe this will help you.

edited May 23 '17 at 12:24

Community

1
1

answered Aug 13 '10 at 14:45

garik

5,669
5
30
42

Hey Igor, yup as you can see in the stored procedure above I am i.e @comments NVARCHAR(MAX) = default But no joy – StevieB Aug 13 '10 at 14:49
What about field in the table? – garik Aug 13 '10 at 14:51
Yeah I changed that to NVARCHAR(MAX) – StevieB Aug 13 '10 at 14:52
So your saying when I use NVARCHAR theres no need for me to look at add N before the value when inserting into DB – StevieB Aug 13 '10 at 14:53
@StevieB - the N just makes whatever follows in the single quotes in unicode format. – JNK Aug 13 '10 at 14:54
@StevieB How do you call this stored procedure? – garik Aug 13 '10 at 14:54
@JNK Yeah I think I need that though in my stored procedures because Im having issues saving russian characters i.e if i do plain sql UPDATE tblContactUs SET comments = N'Админ' WHERE (id = 2228) it saves fine and im trying to get this same logic in a stored procedure with a variable but proving impossible – StevieB Aug 13 '10 at 14:56
@igor in classis ASP Dim comments : comments = database_FilterInput(request.Form("comments")) Set objSP = SQLGetProcedure("spInsertContactUs") ... SQLSetProcedureParam objSP, "comments", comments ... – StevieB Aug 13 '10 at 14:57
What is `SQLSetProcedureParam`? I can't see many google results for that. Maybe use SQL profiler to see what your asp application is sending to the server. – Martin Smith Aug 13 '10 at 15:01
AH sorry its a customer method..but when I print out to the screen the value of comments before it goes into the stored procedure the value is Админ. So no issue on the server I think. I just need to find a way to get a N in front of that variable – StevieB Aug 13 '10 at 15:04
@StevieB I have added link to my answer. please, see on it. maybe it is a problem asp, i think. luck – garik Aug 13 '10 at 15:05
@StevieB - Just because the screen shot shows Russian characters doesn't mean that's how it's being passed back to the database. My guess is that it's being converted away from unicode somewhere between when it is in the form field and when it gets passed to the server. SQL Profiler should be able to confirm this for you. – Tom H Aug 13 '10 at 15:11
@igor Yup that worked ! <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> added to top of page + changing column type to NVARCHAR did the trick – StevieB Aug 13 '10 at 15:11
@Tom H. Yup your right, just realised this to. And also whats SQL Profiler ? – StevieB Aug 13 '10 at 15:12
SQL Profiler is a tool that comes with MS SQL Server. It lets you monitor exactly what is coming into the server. You can filter which events you track so you could see the exact stored procedure call as the server is seeing it. It can be a very useful debugging tool in these situations. – Tom H Aug 13 '10 at 15:24
Tom H. I.e do i need to keep watching it in real time waiting for inserts to be done or how would that work i.e can i run some dummy values in it – StevieB Aug 13 '10 at 15:26

score 0 · Answer 2 · edited Feb 04 '17 at 07:34

0

This works. I figured it out myself:

Command.Parameters.Add("@prc", SqlDbType.NVarChar).Value = strPrc;

You need to change your SqlDbType into NVarChar. You also need to change the field into Nvarchar field and the stored procedure into that too.

edited Feb 04 '17 at 07:34

Joe C

15,324
8
38
50

answered Feb 03 '17 at 18:48

Xiayi

1

score -1 · Answer 3 · edited May 23 '16 at 07:54

-1

declare @a nvarchar(50)
set @a =N'تست'

exec('INSERT INTO [dbo].[tblP] ([productVal],[name],[month])  VALUES ( 9, '+'N'''+@a+''',1)')
INSERT INTO [dbo].[tblP] ([productVal],[year],[month])  VALUES ( 9, N'تست',1)

edited May 23 '16 at 07:54

DanielBarbarian

5,093
12
35
44

answered May 23 '16 at 06:31

arezoo

1

Please provide some explanation in addition to the code. – Jan May 23 '16 at 06:33

SQL use Unicode N in a Stored Procedure with variable

3 Answers3