How to use openquery in stored procedure?

Question

I have to pass parameters to a linked server and I have to use open query, I have a code as below:

DECLARE @TSQL varchar(8000), @VAR char(2)

SELECT @VAR = 'CA'

SELECT @TSQL = 'SELECT * INTO newtable FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

Now I need to use it in a stored procedure but I get an ambiguous syntax error. How I can use it in a stored procedure?

Answer 1

Try this:

CREATE PROCEDURE [Name]

AS

DECLARE @TSQL varchar(8000), @VAR char(2)
SET @VAR = 'CA'

SET @TSQL = 'SELECT * INTO newtable FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ' + @VAR + ' '')'

EXEC (@TSQL)