Dynamic ORACLE SP for calling a linked server database environment based on a parameter.
I haven't posted anything here yet on this blog, so I thought I'd pull some lesson I learned a while back and use it as a post. If it helps you; great, if not, no worries.
The problem I tried to solve involved 5 or 6 Oracle environments that need to have data pulled from them into SQL Server. The user knows what environment they want to query off of, so this SP gives them the flexibility they need. I actually used this as a SP for a SSRS report. I hide the Oracle Parameter in SSRS so that only the users with technical experience could change this parameter, but you can make it a live parameter too.
-- =============================================
-- Author: Keith Ramsey
-- Description: Dynamic Environment SP to report from any linked server passed through your parameter.
-- =============================================
CREATE PROCEDURE [dbo].[uspDynamicLinkServerSP]
@ORACLE_ENVIRONMENT VARCHAR (5)
AS
BEGIN
-- Create variables for the view that you read in ORACLE, for
-- the run of the SQL Statement and for the com SQL String.
DECLARE @ORACLE_VIEWNAME VARCHAR (45)
DECLARE @SQLSTRING VARCHAR (1000)
DECLARE @S2 NVARCHAR(1000)
Set @Oracle_VIEWNAME = '..IMSV7.tableORviewName'
SET @SQLSTRING = 'SELECT * from '
SET @SQLSTRING = @SQLSTRING + @ORACLE_ENVIRONMENT + @ORACLE_VIEWNAME
SELECT @S2 = CAST(@SQLSTRING AS NVARCHAR(1000))
execute sp_executesql @S2
END