I have stored proc which send string w/quotes works ok:
ALTER PROCEDURE [dbo].[Proc] @Store char(5)
AS
BEGIN
SELECT * FROM OPENQUERY(Server, 'SELECT * FROM T WHERE Store = ''MA009'' GROUP BY Store')
END
How insert @Store in openquery string? below not working
ALTER PROCEDURE [dbo].[Proc] @Store char(5)
AS
BEGIN
SELECT * FROM OPENQUERY(Server, 'SELECT * FROM T WHERE Store = '''+ @Store + ''' GROUP BY Store')
END
According to MSDN,
OPENQUERY does not accept variables for its arguments.
However, you can try the below;
DECLARE @SQL varchar(8000), @Store char(5)
SELECT @Store = 'MA009'
SELECT @SQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM T WHERE Store =''''' + @Store + ''''' GROUP BY Store '')'
EXEC (@SQL)
OR you can use the Sp_executesql Stored Procedure
To avoid the multi-layered quotes, use code that is similar to the following sample:
DECLARE @Store char(5)
SELECT @Store = 'MA009'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM T WHERE Store = @var',
N'@var char(5)',
@Store
It is not possible to use variables in OPENQUERY. But you can use a variable with following Workaround:
DECLARE @test nvarchar(MAX);
SET @test = 'MyStringValue';
DECLARE @query nvarchar(max) =
'SELECT * FROM OPENQUERY(adamm_db,''Select *
FROM databaseName.schemaName.table
WHERE id = ''''' + @test + ''''''')'
EXEC(@query)
