Using a Variable in OPENROWSET Query

sql sql-server sql-server-2008 openrowset
Control Freak picture Control Freak · Dec 12, 2012 · Viewed 77.6k times · Source

I'm having trouble with this query:

SELECT * 
FROM OPENROWSET(
    'SQLNCLI',
    'DRIVER={SQL Server};',
    'EXEC dbo.sProc1 @ID = ' + @id 
 )

Gives an error:

Incorrect syntax near '+'.

Anyone know why I'm getting this error?

Answer

praveen picture praveen · Dec 12, 2012

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)

Related questions

SQL SERVER bulk insert ignore deformed lines

I have to import SAP unconvered lists. These reports look quite ugly and are not that well suited for automated processing. However there is no other option. The data is borderd around minus and pipe symbols similar to the following …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More