Using a Variable in OPENROWSET Query

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)