Openquery with variable

Posted on 2011-10-17
Last Modified: 2012-05-12
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')
Question by:lrbrister
    LVL 32

    Expert Comment

    by: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

    LVL 75

    Accepted Solution

    You cannot use variables, so you will have to resort to Dynamic SQL.
    LVL 75

    Expert Comment

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


    Author Closing Comment

    Thanks...put me on the right road.

    Final code

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

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

          If exists (Select from #temp tt where and tt.AVAILABLE = 0)
                      Set @msg = 'sorry';
                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';            

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now