How to use dynamic SQL in Fabric Warehouse

[kkoba]

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

[v-huijiey-msft]

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 · Accepted solution]

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.

[kkoba]

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

[v-huijiey-msft]

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.

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!

[AndyDDC]

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.