Exec SP with OpenQuery & parameter

Transact-SQL
1

SP as below. It is using 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 SP with parameter 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 SP.

2

becaue here is no bank_sn named @param01. Build the query outside of where you have it now using dynamic query and then use the dynamic query variable in the openrowset

declare @query varchar(max)
set @query = "select xyz from abc where
bank_sn '" + @param01 + "' "
openrowset(linked, @query)