Home > TSQL > SQL Tip #1:- Dynamic SQL hold SQL query in the variable must have NVARCHAR Data type

SQL Tip #1:- Dynamic SQL hold SQL query in the variable must have NVARCHAR Data type

In Dynamic SQL query the Query which is stored in the variable should have NVARCHAR Data type.

Let see:-

When we run the below query with data type VARCHAR

DECLARE

@Sql varchar(max)=”

,@Region Varchar(50)=”’IND”,”USA”’

SET @Sql=’SELECT C.CountryName,sum(F.SalesAmount) SalesAmount

FROM DBO.FactSales F

INNER JOIN DimCountry C

ON F.CountryID=C.CountryID

WHERE C.CountryCode in (‘ +@Region +’)

GROUP BY C.CountryName’

EXEC sp_executesql @sql

 

Above Query gives below error:-

Result: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.

 

When we run the below query with data type NVARCHAR

DECLARE

@Sql Nvarchar(max)=”

,@Region Varchar(50)=”’IND”,”USA”’

SET @Sql=’SELECT C.CountryName,sum(F.SalesAmount) SalesAmount

FROM DBO.FactSales F

INNER JOIN DimCountry C

ON F.CountryID=C.CountryID

WHERE C.CountryCode in (‘ +@Region +’)

GROUP BY C.CountryName’

EXEC sp_executesql @sql

Above query gives output:-

Result:-

CountryName SalesAmount
INDIA 2500
UNITED STATES OF AMERICA 9000

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: TSQL Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: