CODEWITHSUNDEEP
sqlsql-server-2008nvarchar
Rijitha
Rijitha

Reputation: 29

Local language not supported in Dynamic inline SQL query (dynamically not able to add N before the local language columns)

Table Name : Customer

customerID VARCHAR(20),
CustomerName NVARCHAR(100)
City NVARCHAR(100)
Country NVARCHAR(100)

CustomerName,City,Country columns will have local languages.

For Search functionality we need to form dynamic Query like below ,

SELECT CostomerID 
FROM customer
 WHERE CITY=N'New York'

So in SP we have code like below,

SET Query= 'SELECT CostomerID FROM customer WHERE ' + @columnName + '=' + @Value

exec Query

The Problem is Since City Column have local language we need to add N prefix in the values dynamic query , but we are not able to add the N in the dynamic Query

Sample Code

DECLARE @columnName NVARCHAR(200) SET @columnName='CITY'

Expected DECLARE @Value1 NVARCHAR(200) SET @Value1=N'சென்னை' PRINT(@Value1) DECLARE @statement NVARCHAR(500) SET @statement= 'SELECT CostomerID FROM customer WHERE ' + @columnName + '=N''' + @Value1 +'''' PRINT (@statement)

Autual DECLARE @Value2 NVARCHAR(200) SET @Value2='சென்னை' PRINT(@Value2) DECLARE @statement NVARCHAR(500) SET @statement= 'SELECT CostomerID FROM customer WHERE ' + @columnName + '=N''' + @Value2 +'''' PRINT (@statement)

Tried
DECLARE @Value3 NVARCHAR(200) SET @Value3='N'+'சென்னை' PRINT(@Value3) DECLARE @statement NVARCHAR(500) SET @statement= 'SELECT CostomerID FROM customer WHERE ' + @columnName + '=N''' + @Value3 +'''' PRINT (@statement)

Upvotes: 0

Views: 225

Answers (1)

Kevin Martin
Kevin Martin

Reputation: 146

When you build your statement you aren't doing anything to put quote marks around the string literal that is supposed to be the value to look for. You didn't say what you actually got but I expect it is something like:

SELECT 1 FROM customer WHERE City=Nசென்னை

If you expect to get:

SELECT 1 FROM customer WHERE City=N'சென்னை'

you need to build the statement this way:

SET @statement= 'SELECT CostomerID FROM customer WHERE ' + @columnName + '=N'''  + @Value + ''

This will fail if @Value happens to contain single quote marks. A better way would be to define a function that takes a NTEXT value and returns the string literal representation with preceding N, surrounding quotes, and any internal quotes doubled (and anything else I've forgotten).

Upvotes: 0

Related Questions