Friday, August 9, 2013

Using N before SQL staring

Using N before SQL staring

Introduction 

A lot of query that I find is why we use N before any SQL string. Here in this article I am trying to
answer this question with some example


What N Denotes
When we use N before any SQL string it denotes that the SQL sub string is an UNICODE. Here N means that the National Language character set.

If we use N with any substring that means that we are passing the value of a variable with data type like nVARCHAR,  nCHAR,  nTEXT.

We never pass the value of variable with N prefix that have data type CHAR, VARCHAR, TEXT.

Take a Simple Example

Try this sql

Exec sp_executesql 'SELECT ''Joyadeep'' As NAME'

It gives an error

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Here the sp_executesql expect an UNICODE staring.

Now try this

Exec sp_executesql N'SELECT ''Joydeep'' As NAME'

NAME
--------
Joydeep

(1 row(s) affected)

We can use variable also

DECLARE @SQLSTRING nVARCHAR(max);
SET @SQLSTRING = N'SELECT ''Joydeep'' AS NAME';
Exec sp_executesql @SQLSTRING;

NAME
-------
Joydeep

(1 row(s) affected)



Hope you like it.