goofyui goofyui - 2 months ago 24
SQL Question

How to pass variable in the Bulk openrowset syntax?

How to pass a variable to the bulk

openrowset
command?

SELECT *
FROM OPENROWSET(BULK @filename, SINGLE_BLOB) AS x;


I need to dynamically pass a file path to the
openrowset
command.

Answer Source

I don't think you can. I believe you have to use dynamic sql as in the following example:

DECLARE @FileNameWithPath VARCHAR(max)
DECLARE @FileText VARCHAR(max)
DECLARE @FileId INTEGER
DECLARE @DynamicSQLMask AS NVARCHAR(max);
DECLARE @DynamicSQL AS NVARCHAR(max);
DECLARE @dynamicparamdec AS NVARCHAR(max);

--This is a string we'll use for our SQL Command.  
--  The ''##FileAndPath##'' is a target string we will replace as we loop 
--  over the file.
SET @DynamicSQLMask = ' SELECT @output = BulkColumn
            FROM  OPENROWSET(BULK ''##FileAndPath##'', SINGLE_BLOB) AS x'

DECLARE #MyCursor CURSOR FAST_FORWARD
FOR
SELECT x.FileId
FROM Files x


OPEN #MyCursor

FETCH #MyCursor
INTO @FileId

WHILE @@fetch_status = 0
BEGIN
    BEGIN TRY

    -- This is setup to read a number of files.  This is where you would
    -- Set the filename with path each time in the loop.  For Example
    -- Select @FileNameWithPath = 'C:\temp\myfile.txt' 

    SELECT @FileNameWithPath = FT.[DataFilesPath] + '\' + Finfo.X12FileName
    ,      @DoNotProcess = FInfo.X12Fields
    FROM Files x 
    WHERE x.FileId = @FileId

    -- Here we are building the SQL we're going to execute.  Sticking with
    -- The example, @FileNameAndPath would hold 'C:\Temp\myfile.txt'
    -- So it would replace '##FileAndPath##'  'C:\Temp\Myfile.txt'.  
    -- At which point @DynamicSQL would hold 
    -- ' SELECT @output = BulkColumn
    --     FROM  OPENROWSET(BULK ''C:\Temp\Myfile.txt'', SINGLE_BLOB) AS x'

    SELECT @DynamicSQL = replace(@DynamicSQLMask, '##FileAndPath##', 
    @FileNameWithPath)

    -- This is where you're setting up a parameter for our dynamic SQL.  
    -- It is an output parameter so it would return the text it reads from 
    -- the file.  Read up on sp_ExecuteSql and passing parameters
    -- if this part is confusing.
    SET @dynamicparamdec = '@output varchar(max) output'

    EXECUTE sp_executesql @DynamicSQL
    ,                     @dynamicparamdec
    ,                     @FileText OUTPUT


    -- Here we store the contents of the file we read into a table.
    UPDATE Files
    SET [FileText] = @FileText
    WHERE FileId = @FileId
    END TRY

    BEGIN CATCH
    select 'load failed code here'
    END CATCH

    FETCH #MyCursor
    INTO @FileId
END -- End the Cursor Loop. 

--Close the cursor, if it is empty then deallocate it:
IF (
    SELECT CURSOR_STATUS('global', '#MyCursor')
    ) >= - 1
BEGIN
    IF (
        SELECT CURSOR_STATUS('global', '#MyCursor')
        ) > - 1
    BEGIN
        CLOSE #MyCursor
    END

    DEALLOCATE #MyCursor
END