How to use variables in select in Synapse Analytics?

Question

DECLARE @ii VARCHAR(2000) = 'adl://bingads-algo-prod-networkprotection-c08.azuredatalakestore.net/shares/adPlatform.AudienceIntelligence.Scoring.Prod/PublicShare/UserOffer_SearchAndAIM/Part2_AIM_30PastDays/Debug/DomainUserSamplePath_Latest.ss';
SELECT TOP 100 UserID
    FROM OPENROWSET(BULK @ii, FORMAT = 'SStream',PARSER_VERSION = '2.0') AS a

Incorrect syntax near '@ii'.

Why can't I use variables after FROM OPENROWSET(BULK @ii ?.
How should I use variables here?
Ths

Answer 1

Hello @Andrew Huang (Beyondsoft Corporation) ,

Thanks for the question and using MS Q&A platform.

When I tried initially, I had faced the same problem shown above:

As per the SO thread post: OPENROWSET requires string literals, not expressions.

For more details, refer to OPENROWSET (Transact-SQL).

You cannot use expressions in OPENROWSET. Try creating a dynamic sql to pass the parameters as shown below:

DECLARE @ii VARCHAR(1000)='https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv';  
DECLARE @command nvarchar(1000)  
set @command = N'SELECT  * FROM OPENROWSET(  
    BULK ''' + @ii + ''',  
    FORMAT = ''CSV'',  
    PARSER_VERSION = ''2.0'',  
    HEADER_ROW = TRUE) as [r]'  
EXEC sp_executesql @command  

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.