Exec stored procedure with OpenQuery & parameter

Question

Stored procedure as below. It is using a linked server to Oracle.

ALTER PROCEDURE [dbo].[SVOPS_ZsmartPaymentView]
    (@param01 VARCHAR(255))
AS
BEGIN
    SELECT * 
    FROM OPENQUERY(ZSMART_PROD, 'SELECT BANK_SN FROM CC.V_PAYMENT WHERE BANK_SN=''@param01''')
END

If I call the stored procedure with parameters as shown below, the result is empty:

EXEC [dbo].[SVOPS_ZsmartPaymentView] @param01 = 'T131612073600'

But if I run the statement directly like below, there is result.

SELECT * 
FROM openquery(ZSMART_PROD, 'SELECT * FROM CC.V_PAYMENT WHERE BANK_SN=''T131612073600''')

Any idea why it returned empty result if executed using the stored procedure?

possible duplicate https://stackoverflow.com/questions/3378496/including-parameters-in-openquery — Peeyush, Jun 05 '18 at 04:53
@Peeyush, it is understandable that you are eager to contribute (and gain credits), but this is **DEFINITELY NOT** a _possible duplicate_. — FDavidov, Jun 05 '18 at 05:01
@FDavidov if you please read the question in link carefully, you will find that the answer to the linked question solves the actual question. If I wanted credits I would have answered instead of commenting with the link. (Check the Pass Basic Values part of the answer of the linked question) — Peeyush, Jun 05 '18 at 05:21

score 0 · Answer 1 · answered Jun 05 '18 at 05:03

0

Try this:

ALTER PROCEDURE [dbo].[SVOPS_ZsmartPaymentView]
    (@param01 VARCHAR(255))
AS
BEGIN
    SELECT * 
    FROM OPENQUERY(ZSMART_PROD, 'SELECT BANK_SN FROM CC.V_PAYMENT WHERE BANK_SN=''' + @param01 + '''')
END

answered Jun 05 '18 at 05:03

FDavidov

3,505
6
23
59

score 0 · Answer 2 · answered Jun 06 '18 at 13:58

I follow the sample from https://support.microsoft.com/en-us/help/314520/how-to-pass-a-variable-to-a-linked-server-query.

And it works.

Pass Basic Values

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample:

DECLARE @TSQL varchar(8000), @VAR char(2) SELECT @VAR = 'CA' SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')' EXEC (@TSQL)

Thanks.

Exec stored procedure with OpenQuery & parameter

2 Answers2