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
Why Is Null Not Equal to Null False
Pivot Table and Concatenate Columns
Show a One to Many Relationship as 2 Columns - 1 Unique Row (Id & Comma Separated List)
Conditional Sum in Group by Query Mssql
How to Convert Rows to Columns in Oracle
Why (And How) to Split Column Using Master..Spt_Values
Preventing Adjacent/Overlapping Entries with Exclude in Postgresql
How Would You Implement Sequences in Microsoft SQL Server
Transpose Rows into Columns in Bigquery (Pivot Implementation)
For Nvarchar(Max) I am Only Getting 4000 Characters in Tsql
Does Anyone Use Right Outer Joins
Change Postgresql Columns Used in Views
Create Trigger to Log SQL That Affected Table
Why Does This SQL Code Give Error 1066 (Not Unique Table/Alias: 'User')
Does Sparksql Support Subquery