Below is SQL stored procedure I use to get data from a Oracle table (using Lined Server) and then display it in a SSRS report drop down parameter.
Could I create this stored procedure in Oracle then call the Oracle stored procedure from my SQL stored procedure.
I would like to eliminate the inserting of the Oracle data into a temporary table then running a Select over the temporary table.
I would rather execute a Oracle stored procedure from the SQL stored procedure that just does the Select (if possible).
I am guessing that would save some time and I do this in several stored procedures over much larger Oracle tables.
The Oracle table is called GPCOMP1.GPPROB.
Also, what would the Oracle stored procedure look like (just to get me started in right direction).
Any help is appreciated.
ALTER PROCEDURE [NonFreight].[procRptLstSalesArea]
SET NOCOUNT ON;
DECLARE @sql VARCHAR(MAX)
--Build #SalesArea Sql table
CREATE TABLE [NonFreight].[#SalesArea]
SET @Sql = 'SELECT DISTINCT C.SALESAREA as SalesAreaCode, C.SALESAREA as SalesAreaName
FROM GPCOMP1.GPPROB C'
SET @Sql = N'INSERT INTO [NonFreight].[#SalesArea]
SELECT * from openquery
([GPNF], ''' + Replace(@Sql, '''', '''''') + ''')'
-- Dropdown query
Count(*) OVER(PARTITION BY NULL) AS SalesAreaCount
WHERE SalesAreaName != ''
ORDER BY SalesAreaName