Parameters in OPENROWSET?

[spacedeveloper]

Hi everyone,

I must be missing something simple here, but I can't seem to find it. When I try to run this openrowset query I get an

"Incorrect syntax near '+'."

error near the line mentioned. Can someone help me find the problem? Thanks:

declare @PropID varchar(16);
declare @FolioNum int;
set @PropID = 'PID';
set @FolioNum = 17516;

SELECT 
	a.room
	, a.transactionnumber
	, LTRIM(RTRIM(CONVERT(VARCHAR(10),a.transactiondate,101))) AS transactiondate
	, a.accountnumber
	, ISNULL(a.accountdesc, '') AS accountdesc
	, CAST(a.credit AS decimal(18, 2)) AS Charges
	, CAST(a.debit AS decimal(18, 2)) AS Payments
FROM OPENROWSET
('SQLOLEDB'
, '' + @PropID + ''  --<-------*** ERROR MENTIONS THIS LINE *****
 ; 'userid' ; 
'password'
, 'select * from NV.dbo.Database WITH(NOLOCK) WHERE folionumber = ' + cast(@FolioNum as varchar(10)) + ' ORDER BY transactionnumber, transactiondate')
as a

Thanks for any help,

Frank

 

[TomKane]

Hi Frank,

I think the problem is that the OPENROWSET doesn't like the use of variables. It would probably work if you had the literal value 'PID' rather than '' + @PropID + ''.

One potenital work around would be to prepare the command in a variable and execute it as dynamic SQL. You may be reluctant to go down that path - and that would be completely understandable.

Regards,
Tom

 

[markros]

See

http://social.msdn.microsoft.com/Fo.../thread/0847ad24-0dfe-4ae1-9788-5516c7830f40/

and

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=256&lngWId=5

PluralSight Learning Library

 

[spacedeveloper]

Thanks, I'll take a look at these suggestions and try to test them out.

The way I've been attempting to hit the problem is by dynamic sql...as much as I'd like to avoid it.