title: How do you use OPENQUERY within sp_executesql?
body: <p>I'm trying to verify a job number exists on a linked server and get back a variable (@JobExists) indicating whether it does or not (1 for yes, 0 for no). </p>
  
  <p>To do this I'm trying to use OPENQUERY along with sp_executesql as I have to pass in a parameter for the job number. I've tried the code below but get the error 'Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. For testing purposes I've declared and set the variable @JobNumber.</p>
  
  <pre><code>DECLARE @JobNumber as varchar(50)
  SET @JobNumber = '2112111'
  
  DECLARE @JobExists as BIT
  DECLARE @JobCount as int
  DECLARE @ParmDefinition as varchar(100)
  DECLARE @sql as varchar(500)
  
  SET @JobExists = 0
  SET @ParmDefinition = N'@Result int output'
  SET @sql = 'SELECT @Result = SELECT COUNT(*) FROM OPENQUERY(MYLINKEDSVR,''SELECT JOB_NUMBER FROM PROD.tbl1 WHERE JOB_NUMBER = ''''' + UPPER(RTRIM(LTRIM(@JobNumber))) + ''''''')'
  
  exec sp_executesql @sql, @ParmDefinition, @Result = @JobCount output
  
  IF @JobCount &gt; 0
  SET @JobExists = 1
  
  SELECT @JobExists
  </code></pre>
  
  <p>I've read up sp_executesql here: <a href="http://technet.microsoft.com/en-us/library/ms188001.aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/ms188001.aspx</a>
  I've also done various searches but haven't come across any answers that work for me.</p>
  
  <p>Is there something I'm missing? </p>