question

AndrewHuangBeyondsoftCorporation-5706 avatar image
0 Votes"
AndrewHuangBeyondsoftCorporation-5706 asked PRADEEPCHEEKATLA-MSFT commented

How to use variables in select in Synapse Analytics?

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @AndrewHuangBeyondsoftCorporation-5706,

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.


image.png (45.5 KiB)
image.png (97.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PRADEEPCHEEKATLA-MSFT avatar image PRADEEPCHEEKATLA-MSFT AndrewHuangBeyondsoftCorporation-5706 ·

Hello @AndrewHuangBeyondsoftCorporation-5706,

Glad to know that it helped.

I would suggest you to use MS Q&A platform for any issues related to Azure products.

0 Votes 0 ·