SQL Synapse, use dynamic/parameterized Azure Container in CREATE EXTERNAL TABLE

Question

We have a scenario where the source csv files are isolated by Customer i.e., each Customer will have a Container in the Azure Storage.

When creating External Table in SQL Synapse, is it possible to pass the Container name as parameter that way there are not multiple External Data Tables in SQL Synapse DB?

    CREATE EXTERNAL DATA SOURCE AzureBlobStorage with (  
    TYPE = HADOOP,
    LOCATION ='wasbs://<**container100**>@<accountname>.blob.core.windows.net',  
    CREDENTIAL = AzureStorageCredential  
    ); 

    CREATE EXTERNAL TABLE [dbo].[res1_Data] (  
    [ID] INT,
    [UniqueId] VARCHAR(50),
    [Status] VARCHAR(50) NULL,
    [JoinedDate] DATE
    )  
    WITH (LOCATION='<**container2**>/<folder>/<file>.csv',
    DATA_SOURCE = AzureBlobStorage,  
    FILE_FORMAT = CEFormat  
    ); 

Answer 1

Unfortunately you can't use variables within DDL commands. However, you can build dynamic statements and then execute with sp_executesql to do this.

More information here.