Using a Variable in Openrowset Query

Using a Variable in OPENROWSET Query

As suggested by Scott , you cannot use expressions in OPENROWSET.Try creating a dynamic sql to pass the parameters

Declare @ID int
Declare @sql nvarchar(max)
Set @ID=1
Set @sql='SELECT * 
FROM OPENROWSET(
               ''SQLNCLI'',
               ''DRIVER={SQL Server};'',
               ''EXEC dbo.usp_SO @ID =' + convert(varchar(10),@ID) + ''')'

-- Print @sql
 Exec(@sql)

How to use a variable in Openrowset command

Change your script like below.

DECLARE @file_stream VARBINARY(MAX)
DECLARE @command nvarchar(1000)
DECLARE @filePath NVARCHAR(128)
set @filePath = 'C:\Temp\print4.pdf'

set @command = N'SELECT @file_stream1 = CAST(bulkcolumn AS varbinary(MAX))
                from OPENROWSET(BULK ''' + @filePath + ''',
                SINGLE_BLOB) ROW_SET'

EXEC sp_executesql @command, N'@file_stream1 VARBINARY(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT

select @file_stream

Sample Output :

SQL - OPENROWSET with variable instead of string path

The parametrized dynamic SQL query may not work. Try concatenating the path explicitly:

DECLARE @path varchar(50) = 'C:\xml\hamlet.xml', 
        @sql nvarchar(max)= ''

set @sql = '
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK ''' + @path +''', SINGLE_BLOB) AS x;'

exec sp_executesql @sql,N''

Dynamic SQL: OPENROWSET with @param and INSERT into @Table

Why use dynamic SQL at all and not just use INSERT INTO?

DECLARE @BuildTimes table (BuildTableName varchar(MAX) NULL,
                           BuildDate date NULL);

DECLARE @days int = 0;
DECLARE @startDate date = GETDATE();
DECLARE @buildDate date = GETDATE();

WHILE (@days <= 30)
BEGIN

    SET @buildDate = DATEADD(day, -1*@days, @startDate);    
    INSERT INTO @BuildTimes (BuildTableName,
                             BuildDate)
    EXEC log.BuildTimes @buildDate;

    SET @days = @days + 1;

END;

SELECT BuildTableName,
       BuildDate
FROM @BuildTimes;

How to use a variable in Openrowset to Loop over all XML files

I don't think you can use T-SQL variables in your OPENROWSET command - those things need to be fully and explicitly spelled out.

If you need to do this over a list of XML files, you'll have to create your T-SQL command as a string and then use dynamic SQL to execute that command.

---

Related Topics