Using a Variable in OPENROWSET Query

Question

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 1

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) 

Answer 2

OPENROWSET requires string literals, not expressions. It's complaining about the plus sign, becaue it doesn't expect anything more than a string literal and you follewed the string literal with an operator.

See http://msdn.microsoft.com/en-us/library/ms190312.aspx which states:

'query'

Is a string constant sent to and executed by the provider...