Hi Prabhuram Kumar ,
Welcome to Microsoft Q&A platform and thankyou for posting your question here.
As per my understanding, you are trying to parameterize the folder path in openrowset function in synapse SQL script. Please let me know if that is not the ask.
To parameterize the file/folder directory value in your SQL script, You can use the wildcards in the OPENROWSET part of the query and filter the files in the WHERE clause.
SELECT r.filepath() AS filepath ,r.filepath(1) AS [year] ,r.filepath(2) AS [month] ,COUNT_BIG(*) AS [rows] FROM OPENROWSET( BULK 'csv/taxi/yellow_tripdata_*-*.csv', DATA_SOURCE = 'SqlOnDemandDemo', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIRSTROW = 2 ) WITH ( vendor_id INT ) AS [r] WHERE r.filepath(1) IN ('2017') AND r.filepath(2) IN ('10', '11', '12') GROUP BY r.filepath() ,r.filepath(1) ,r.filepath(2) ORDER BY filepath;
Note that you may need to adjust the file path and file name to match your specific scenario. Also, make sure that the SQL Server instance has the necessary permissions to access the data lake storage account
For more details, kindly check the following documentation: Use file metadata in serverless SQL pool queries
Hope it helps. Kindly accept the answer if it's helpful. Thankyou.