Forum Discussion
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.
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
5 Replies
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
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 TeamIf 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
Most Valuable Professional
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.
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 TeamIf 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!
