Archive

Posts Tagged ‘SQL TIPS’

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

January 20, 2015 Leave a comment

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

Categories: TSQL Tags: ,