CODEWITHSUNDEEP
sql
Maxrem
Maxrem

Reputation: 45

SQL Server variable in string

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

Upvotes: 0

Views: 1406

Answers (2)

Michael Mairegger
Michael Mairegger

Reputation: 7301

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)

Upvotes: 0

Nadeem_MK
Nadeem_MK

Reputation: 7689

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

Upvotes: 1

Related Questions