How to set and use variables in Microsoft fabric DataWareHouse queries

[kkoba]

I would like to know the limitations of using variables in queries in Fabric Datawarehouse.

As shown below, when I set a variable using the SET option in a Datawarehouse query, a warning occurs for the SET option. The query is verified to be working as intended.

So the warning is there but it's working fine?

declare @i INT
DECLARE  varchar(100)

SET @i = 0;
WHILE @i < 100
BEGIN
	SET @i = @i + 1;
	SET  = 'あああ鯖_' + CAST(@i as varchar(100));
	INSERT INTO [WHTest01].[dbo].[table1]([col1],[col2])
	VALUES(@i, )
END

following warning:

Each SQL statement runs as an independent session.

Session context is not preserved between SQL statements. For more information,

see https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-query-editor#limitations.

But I check the DOCS, it looks like local variables and While loops are supported in Fabric DataWareHouse queries.

https://learn.microsoft.com/ja-jp/sql/t-sql/language-elements/set-local-variable-transact-sql?view=f...

https://learn.microsoft.com/ja-jp/sql/t-sql/language-elements/while-transact-sql?view=fabric

thanks you

[v-jingzhan-msft · Accepted solution]

Hi @kkoba 

It's just a warning in the SQL query editor in Fabric portal. The statements you use are supported in Fabric warehouse. 

The document you linked mentions that "You can run Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL) statements, but there are limitations for Transaction Control Language (TCL) statements." INSERT command belongs to DML, so it is supported. 

In addition, some limitations in the linked document are in the SQL query editor in Fabric portal. Some of them are supported in SSMS/ADS.

For the current general limitations for Data Warehousing in Microsoft Fabric, you can learn more from the following documentation:

Limitations - Microsoft Fabric | Microsoft Learn

T-SQL surface area - Microsoft Fabric | Microsoft Learn

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[kkoba]

Hi @v-jingzhan-msft 

Thank you for your reply.

I now understand that this warning is just a warning in the Fabric portal query editor and does not affect the actual query behavior.