Potential Bug in Azure Synapse Analytics

Question

I've encountered what appears to be a bug in which string concatenation fails for queries like "select @Kritivasas =" when trying to concat text with a variable using a select statement from a table in DEDICATED SQL POOLS. Works fine in master db . Two screen grabs included. The first query throws an error. The second is a nearly syntactically identical work around.

Code for screenshot 1:

declare @TableObjectId in = object_id('<your valid table here>'),  
		@RangeValue varchar(50)  
  
declare @sql nvarchar(max)  
  
select @sql = 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] SPLIT RANGE (''' + @RangeValue + ''')'  
from sys.tables t  
  join sys.schemas s on t.[schema_id] = t.[schema_id]  
where t.[object_id] = @TableObjectId  
  
select @sql  

Code for screenshot 2:

declare @TableObjectId in = object_id('<your valid table here>'),  
		@RangeValue varchar(50)  
  
declare @sql nvarchar(max)  
  
set @sql = (  
	select 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] SPLIT RANGE (''' + @RangeValue + ''')'  
	from sys.tables t  
	  join sys.schemas s on t.[schema_id] = t.[schema_id]  
	where t.[object_id] = @TableObjectId  
)  
  
select @sql  

Answer 1

Hello @Lon Fortes ,
Thanks for the ask and using the Microsoft Q&A platform .

This below piece the code should to the trick with the SELECT

declare @tableobjectid int =  object_id('[dbo].[DimReseller]'),  
		@rangeValue varchar(50)  ='100'  
declare @sql nvarchar(max)   
select @sql = 'ALTER TABLE [s.name ] SPLIT Range (''' + @rangeValue +''')  
             from sys.tables t  
			 join sys.schemas s on t.[schema_id] = s.[schema_id]  
			 where t.[object_id] =' + convert(varchar,@tableobjectid)  

print @sql   

Output

ALTER TABLE [s.name ] SPLIT Range ('100')  
             from sys.tables t  
			 join sys.schemas s on t.[schema_id] = s.[schema_id]  
			 where t.[object_id] =2098106515  

Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members