• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

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')
0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
Daniel WilsonCommented:
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

0
 
Anthony PerkinsCommented:
You cannot use variables, so you will have to resort to Dynamic SQL.
0
 
Anthony PerkinsCommented:
From SQL Server's BOL:
OPENQUERY does not accept variables for its arguments.

0
 
lrbristerAuthor Commented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now