asked on

Openquery with variable

I am trying to run an openquery on the linked server website

To do so I need to place @id in the middle of the openquery select

But I'm getting syntax errors on the variable in the openquery

select * from openquery(WEBSITE,'select * from web_leads where id='+cast(@id as varchar(4))+' and available=0')

Daniel Wilson

It would help to see the errors ... but ... if @id needs to be cast as a varchar, it needs quotes:

select * from openquery(WEBSITE,'select * from web_leads where id='''+cast(@id as varchar(4))+''' and available=0')

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

From SQL Server's BOL:
OPENQUERY does not accept variables for its arguments.

Larry Brister

ASKER

Thanks...put me on the right road.

Final code

ALTER PROCEDURE sp_calls_webContactsFirstClick (
                              @id varchar(4),
                              @userName varchar(30))
As

Declare @msg varchar(10)
Declare @email varchar(255)
begin
      Select t.* into #temp from (select * from openquery(WEBSITE,'select * from web_leads')) t;

      If exists (Select tt.id from #temp tt where tt.id=@id and tt.AVAILABLE = 0)
            Begin
                  Set @msg = 'sorry';
            End
      Else
      Begin
            Declare @sql varchar(4000)
            Set @sql = 'update openquery(WEBSITE,''select * from web_leads where id=' + cast(@id as varchar(4)) + ' and available=1'')' + 'set AVAILABLE = 0, USERNAME=''' + @username+''';'
            exec (@sql);
            Set @msg = 'updated';
      End
End