Stored Procedure / SQL variable inside OPENQUERY string

Question

I'm having trouble using OPENQUERY (to fetch data from a linked server) within a stored procedure. I've been reading up on SQL variables but can't seem to get it working so maybe I'm misunderstanding variables/strings in SQL. It's throwing the error "Incorrect syntax near '+'." and I'm not sure what I'm doing wrong.

The code:

ALTER PROCEDURE [dbo].[sp_getPerson]
@myName nvarchar(MAX)
AS
BEGIN
SET NOCOUNT ON;

SELECT     *
    FROM         OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM myTable
WHERE (myName= ''' + @myName + ''')
') AS derivedtbl_1

END

Should this work, in theory?

Answer 1

I'd put the entire query into a variable and execute the variable.

declare @myName nvarchar(MAX)
declare @sqltext varchar(max)


select  @myName = 'Some Name'

select @sqltext='
select * from openquery(MY_LINKED_SERVER,''select * from database.schema.table
where myName = '''+@myName +'''
)'


exec (@sqltext)

If you use the LINKED_SERVER.DATABASE.SCHEMA.TABLE, you could have performance issues depending on table size, etc. because no indexes are used with this query.