Get Dates to Variables from Oracle SQL in SQL Server using Linked Server

Question

I am trying to pass dates to the Sql Server Stored Procedure where i will get the dates from Oracle Database using Linked Server. This is the first time i am working on Sql Server Procedure, Could some one please help me how to get the dates from @Datesql to variables(@BeginDate, @EndDate) to further pass it to stored procedure ?

As this query results in only 1 row, Is it possible to capture the Print results in variables?

When i do Print i was getting the dates in Sql Server.

Any help would be appreciated. Thanks.

USE [dev]
GO

DECLARE @Datesql varchar(1000);
DECLARE @sysdate varchar(30) = Convert(varchar(20),GETDATE(),101);
DECLARE @BeginDate nvarchar(30);
DECLARE @EndDate nvarchar(30);

set @Datesql='SELECT TO_CHAR(A.WW_WEEK_END_DT-6,''YYYY-MM-DD''),TO_CHAR(A.WW_WEEK_END_DT,''YYYY-MM-DD'')  FROM XX_PUNCH_TBL'

print @Datesql
execute(@Datesql) at orclhrdev

--SET @BeginDate='2014-12-01'
--SET @EndDate='2014-12-01'

DECLARE @return_value_Punch int

EXEC    @return_value_Punch = [dbo].[XX_PUNCH] @BeginDate,@EndDate

SELECT  'Return Value' = @return_value_Punch

GO

Community · Accepted Answer · 2017-05-23 12:28:32Z

If you want to copy some values returned by that exec(...) at linkedserver into @BeginDate, @EndDate variables then you could use a tables variable plus SELECT @variabile = Column ... FROM @table_variable thus:

DECLARE @ResultsFromOrclHRDev TABLE (
    BeginDate VARCHAR(10), -- You could try also DATE / DATETIME 
    EndDate VARCHAR(10)
);

INSERT @ResultsFromOrclHRDeb (BeginDate, EndDate)
EXECUTE(@Datesql) AT orclhrdev;

SELECT @BeginDate = x.BeginDate, @EndDate = x.EndDate
FROM @ResultsFromOrclHRDeb x
-- WHERE conditions ?

Note: To store date / time values I would use the proper data type: DATE, DATETIME, SMALLDATETIME, etc. and not NVARCHAR(30).

Also, you could try using OUTPUT parameters with this linked server thus (solution wasn't tested):

set @Datesql='SELECT TO_CHAR(A.WW_WEEK_END_DT-6,''YYYY-MM-DD'')  INTO ?,TO_CHAR(A.WW_WEEK_END_DT,''YYYY-MM-DD'') INTO ? FROM XX_PUNCH_TBL'

-- print @Datesql
execute(@Datesql, @BeginDate OUTPUT, @EndDate OUTPUT) at orclhrdev

-- select @BeginDate AS BD, @EndDate AS END

Note: Please see selected solution for this question.

Collectives™ on Stack Overflow

Get Dates to Variables from Oracle SQL in SQL Server using Linked Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related