How do you use OPENQUERY within sp_executesql?

Question

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).

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.

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 > 0
SET @JobExists = 1

SELECT @JobExists

I've read up sp_executesql here: http://technet.microsoft.com/en-us/library/ms188001.aspx I've also done various searches but haven't come across any answers that work for me.

Is there something I'm missing?

score 2 · Accepted Answer · answered Jan 07 '14 at 09:47

2

The error message is clear: you must declare @sql as nvarchar and not as varchar. The same for @ParamDefinition:

DECLARE @ParmDefinition as nvarchar(100)  
DECLARE @sql as nvarchar(500)

answered Jan 07 '14 at 09:47

Giacomo Degli Esposti

2,392
1
15
20

Thanks for the info. Silly thing for me to miss. I feel I'm getting somewhere but now get the error: Incorrect syntax near the keyword 'SELECT'. (1 row(s) affected) – sr28 Jan 07 '14 at 09:49
SELECT @Result = COUNT(*) – Giacomo Degli Esposti Jan 07 '14 at 09:54
Thanks. Another simple mistake. Apologies, I'll go make another coffee! – sr28 Jan 07 '14 at 09:56

How do you use OPENQUERY within sp_executesql?

1 Answers1