Using Dynamic SQL to execute Select from Openrowset with JSON

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  August 3, 2023 at 10:13 am

  #4251803

  I have some code which will load in and process a JSON file using Select..from Openrowset as follows:

  Declare @JSON varchar(max)
  
  SELECT @JSON = BulkColumn
  FROM OPENROWSET (BULK 'C:\Users\User1\DR\staging\returns\RET02022203.JSON', SINGLE_CLOB) as j
  
  select @JSON

  i tried to turn this into Dynamic SQL  so that I can pass in the path to the JSON file as a variable but the code is not working and the JSON is not being selected

  My attempt at writing this in dynamic SQL is as follows:

  DECLARE     @FilePath                       NVARCHAR(500)  = N'C:\Users\User1\DR\staging\returns\RET02022203.JSON'
  DECLARE     @JSON                           VARCHAR(MAX)
  DECLARE     @sql                            NVARCHAR(MAX)
  
  DECLARE @params NVARCHAR(255) = '@JSON     VARCHAR(MAX) OUTPUT'
  
  SET @SQL = 'SELECT' +  @JSON + ' = BulkColumn  FROM OPENROWSET (BULK ' + '''' +  @FilePath + '''' + ', SINGLE_CLOB) as j'
  PRINT @SQL
      
  EXEC sp_executesql @sql, @params, @JSON = @JSON OUTPUT;
  
  
  SELECT @JSON
  

  What am i doing wrong here ?

• Ken McKelvey

  SSCoach

  Points: 18896

  More actions

  August 3, 2023 at 10:53 am

  #4251814

  DECLARE     @FilePath                       NVARCHAR(500)  = N'C:\Users\User1\DR\staging\returns\RET02022203.JSON'
  DECLARE     @JSON                           NVARCHAR(MAX)
  DECLARE     @sql                            NVARCHAR(MAX)
  
  SET @SQL = 'SELECT @JSON = BulkColumn  FROM OPENROWSET (BULK ' + '''' +  @FilePath + '''' + ', SINGLE_CLOB) as j'
  PRINT @SQL
      
  EXEC sp_executesql @sql, '@JSON nvarchar(MAX) OUTPUT', @JSON = @JSON OUTPUT;
  
  PRINT @JSON;