SQL Server linked server execution with parameter stored procedure

SQL Server links to the Oracle database via a linked server, and I'm going to write a stored procedure on the SQL Server database that uses the linked service to fetch data from the Oracle database, which is a stored procedure with parameters. Create a stored procedure in the SQL SERVER database to fetch the data from a table in the Oracle database as follows:

1. Create a stored procedure on a SQL Server database

Use [PRODDB]
GO

SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
--Author: <Author,,Name>
--Create Date: <create date,,>
--Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo]. [Test_getdata_from_oracle]
--Add The parameters for the stored procedure here
@P_AS_OF_birthday datetime
As
BEGIN
DECLARE @E_SQL varchar (2000);
--SET NOCOUNT on added to prevent extra result sets from
--interfering with SELECT statements.

Set @E_SQL = ' INSERT INTO Test ' +
' SELECT * FROM OPENQUERY (oracle11g, "select * from test.test where birthday =" "
+ CONVERT (varchar, @P_AS_OF_birthday, 111) + "";
Print @E_SQL;
Execute (@E_SQL);
END

GO

2, execute the stored procedure
exec test_getdata_from_oracle ' 1979-01-01 '

Note: For details on how to establish an Oracle linked server, please refer to the blog post:http://blog.csdn.net/waterxcfg304/article/details/43162403

SQL Server linked server execution with parameter stored procedure