Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us at FabCon Vienna from September 15-18, 2025, for the ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM. Get registered

Reply
kkoba
Frequent Visitor

How to use dynamic SQL in Fabric Warehouse

I want to use dynamic SQL in Fabric Warehouse.

 

Looking at the docs, it seems that dynamic SQL is supported in Fabric Datawarehouse.

https://learn.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-executesql-tr...

 

The syntax of sp_executesql requires statements and parameters to be defined as NVARCHAR type, but I believe Fabric does not support NVARCHAR type.

https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types

 

The following SQL fails.

DECLARE @area varchar(10)
DECLARE @SQL varchar(2000)
DECLARE @param varchar(2000)

SET @SQL = '
SELECT [Area], [Category], SUM(CalcValue) AS sum_CalcValue, COUNT(*) AS [Count] 
FROM [dbo].[TestFact1] WHERE [Area] = @area
GROUP BY [Area], [Category]
ORDER BY [Area], [Category]
'
SET @param = '@Area varchar(10)'
SET @area = 'Area1'

EXECUTE sp_executesql
	@SQL,
	@Param,
	@Area = @area
;

Message 214, Level 16, State 2, Procedure sp_executesql, Line 1 [Batch Start Line 0]
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

 

The following SQL succeeds.

DECLARE @area nvarchar(10)
DECLARE @SQL nvarchar(2000)
DECLARE @param nvarchar(2000)

SET @SQL = '
SELECT [Area], [Category], SUM(CalcValue) AS sum_CalcValue, COUNT(*) AS [Count] 
FROM [dbo].[TestFact1] WHERE [Area] = @area
GROUP BY [Area], [Category]
ORDER BY [Area], [Category]
'
SET @param = '@Area varchar(10)'
SET @area = 'Area1'

EXECUTE sp_executesql
	@SQL,
	@Param,
	@Area = @area
;

 

When using dynamic SQL in Fabric Datawarehouse, is it correct to define variables as NVARCHAR type?

 

thank you

1 ACCEPTED SOLUTION
AndyDDC
Super User
Super User

Hi @kkoba set your variable to type NVARCHAR.  NVARCHAR is indeed not supported in table column datatypes, but you can use variables of type NVARCHAR.

 

AndyDDC_0-1731492006536.png

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @kkoba ,

 

Thanks for the reply from AndyDDC . Your consideration makes sense as an alternative, but if there is a better way, please take it.

 

Did the above suggestions help with your scenario? if that is the case, you can consider accept the helpful suggestions to help others who faced similar requirements.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

AndyDDC
Super User
Super User

Hi @kkoba set your variable to type NVARCHAR.  NVARCHAR is indeed not supported in table column datatypes, but you can use variables of type NVARCHAR.

 

AndyDDC_0-1731492006536.png

 

kkoba
Frequent Visitor

Hi @AndyDDC 

 

Thank you for your reply


@AndyDDC wrote:

set your variable to type NVARCHAR.  NVARCHAR is indeed not supported in table column datatypes, but you can use variables of type NVARCHAR.

Thank you for letting me know that although the NVARCHAR type cannot be set for a table column, it can be declared as the data type of a query variable.
I also declared an NVARCHAR type variable and was able to execute dynamic SQL without any problems, so I thought it might be possible to declare it as a variable.
I wish the official documentation would include that information...


thanks you

 

Anonymous
Not applicable

Hi @kkoba ,

 

NVARCHAR data type is not supported in Fabric Warehouse and sp_executesql command requires NVARCHAR.

 

I have an alternative approach:

 

Try to define all variables and parameters as VARCHAR and do string concatenation directly in SQL script instead of using parameterized form.

 

Code:

DECLARE @area VARCHAR(10)
DECLARE @SQL VARCHAR(2000)
SET @area = 'Area1'
SET @SQL = '
SELECT [Area], [Category], SUM(CalcValue) AS sum_CalcValue, COUNT(*) AS [Count]
FROM [dbo].[TestFact1] WHERE [Area] = ''' + @area + '''
GROUP BY [Area], [Category]
ORDER BY [Area], [Category]'
EXECUTE(@SQL)

 

This worked for me.

vhuijieymsft_0-1731464005577.png

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

I would add to not suggest EXECUTE/EXEC(@SQL) as there is a higher risk of SQL injection attacks.  As sp_executesql is paramterised, this lowers the risk of malicious attacks.

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June FBC25 Carousel

Fabric Monthly Update - June 2025

Check out the June 2025 Fabric update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors