How to use variables in select in Synapse Analytics?

2021-08-27T08:46:08.187+00:00
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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,566 questions
0 comments No comments
{count} votes

Accepted answer
  1. PRADEEPCHEEKATLA-MSFT 83,301 Reputation points Microsoft Employee
    2021-08-30T10:45:03.91+00:00

    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:

    127606-image.png

    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  
    

    127588-image.png

    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.


0 additional answers

Sort by: Most helpful