CODEWITHSUNDEEP
sql-server-2005stored-proceduresopenquery
valoukh
valoukh

Reputation: 561

Stored Procedure / SQL variable inside OPENQUERY string

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?

Upvotes: 2

Views: 8860

Answers (2)

jabs
jabs

Reputation: 1848

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.

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

all you have to do is add a linked server thru a script

http://msdn.microsoft.com/en-us/library/ms189811.aspx sp_addlinkedsrvlogin

if your linked server is TEST-SQL and database is test01 and table name is testtb

and....

query it from another server is something like this.

select * from [TEST-SQL].test01.dbo.testtb linktedTB where ....

and you can also join tables to linked server.

Upvotes: 0

Related Questions