Amu
Reputation: 41
Stored procedure not working properly via EXECUTE but works perfectly as a giant SQL query
There is a stored procedure that runs fine when I copy the whole thing as a SQL query and run it, but that exact same procedure with the same parameters using an EXECUTE command ouputs the WRONG data!
EXECUTE qrm_pulsar_sandbox.[dbo].[sp_PL_Hedge] @CurrentDate = '12/17/2014', @PriorDate = '12/16/2014'
The following is the first part of the stored procedure as a query:
DECLARE @CURRENTDATE AS DATE
DECLARE @PRIORDATE AS DATE
SET @CURRENTDATE = '12/17/2014'
SET @PRIORDATE = '12/16/2014'
---------------Erroneous trades--------------
IF OBJECT_ID('tempdb..#ErrorTrades') is not null DROP TABLE #ErrorTrades
Select *
into #ErrorTrades
from [qrm_pulsar].dbo.PnL_Tradeshistory
where [CreationDt]<=@CurrentDate and [CreationDt]>= @PriorDate
and [DataVer]='0' and [Loan/Pool/Ticket Number]='1405200007' and [Portfolio ID] ='3'
--------------------all trades-------------------------
IF OBJECT_ID('tempdb..#All_Trades') is not null DROP TABLE #All_Trades
select a.*,
case when a.[OptType] in ('Call','Put') and a.[Instrument name] like '%T-Note%' then 'Options on Futures'
when a.[OptType] in ('Call','Put') then 'MBS Options'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%Treasury%' then 'Bond Futures'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%Eurodollar%' then 'Eurodollar Futures'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FN%' and right(rtrim(a.[Instrument Name]),1)='C' then 'Cash Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%GN%' and right(rtrim(a.[Instrument Name]),1)='C' then 'Cash Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FH%' and right(rtrim(a.[Instrument Name]),1)='C' then 'Cash Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FN%' then 'MBS Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%GN%' then 'MBS Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FH%' then 'MBS Forwards'
else' Error'end 'Type'
,Case
when a.[opttype]='Put' and a.[FACE]>0 then 'S'
when a.[opttype]='Call' and a.[FACE]<0 then 'S'
when a.[opttype]=''and a.[FACE]<0 then 'S'
else 'B'end 'Sell/Buy'
,case
when a.[Loan/Pool/Ticket Number] in ('1403040003','1401060002','140724TYU1','140724TYU2','140926TYZ5') and a.[Portfolio ID]='7' then 'TRS-MSR'
when a.[Loan/Pool/Ticket Number] in ('1401060002','1401070001','1312200001','1402040015','1403040019','140724TYU1','140724TYU2','140926TYZ6') and a.[Portfolio ID]='1' then 'PLS-MSR'
when (rtrim(a.[Management]) <>'MSR' or a.[Management]is null)and a.[Portfolio ID]='1' then 'PLS-Pipeline'
when (rtrim(a.[Management]) <>'MSR' or a.[Management]is null) and a.[Portfolio ID]='7' then 'TRS-Pipeline'
when rtrim(a.[Management]) ='MSR' and a.[Portfolio ID]='1' then 'PLS-MSR'
when rtrim(a.[Management]) ='MSR' and a.[Portfolio ID]='7' then 'TRS-MSR'
when a.[Portfolio ID]='2' then 'TRS-Jumbo Acquisition'
when a.[Portfolio ID]='3' then 'MBS Securities'
when a.[Portfolio ID]='4' then 'Jumbo Securities'
end 'Book'
into #All_Trades
from [qrm_pulsar].dbo.PnL_Tradeshistory a left join #ErrorTrades b
on a.[Loan/Pool/Ticket Number]=b.[Loan/Pool/Ticket Number] and a.[Portfolio ID]=b.[Portfolio ID]
where a.[CreationDt]<=@CurrentDate and a.[CreationDt]>= @PriorDate
and a.[DataVer]='0' and b.[Loan/Pool/Ticket Number] is null
--Select * from #All_Trades where [Type]='Error'
--------------------all trades no timing restriction to get type------------------------
IF OBJECT_ID('tempdb..#All_Trades2') is not null DROP TABLE #All_Trades2
select distinct
a.[Portfolio ID],
a.[Loan/Pool/Ticket Number],
case when a.[OptType] in ('Call','Put') and a.[Instrument name] like '%T-Note%' then 'Options on Futures'
when a.[OptType] in ('Call','Put') then 'MBS Options'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%Treasury%' then 'Bond Futures'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%Eurodollar%' then 'Eurodollar Futures'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FN%' and right(rtrim(a.[Instrument Name]),1)='C' then 'Cash Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%GN%' and right(rtrim(a.[Instrument Name]),1)='C' then 'Cash Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FH%' and right(rtrim(a.[Instrument Name]),1)='C' then 'Cash Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FN%' then 'MBS Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%GN%' then 'MBS Forwards'
when a.[OptType] not in ('Call','Put')and a.[Instrument name] like '%FH%' then 'MBS Forwards'
else' Error'end 'Type'
,Case
when a.[opttype]='Put' and a.[FACE]>0 then 'S'
when a.[opttype]='Call' and a.[FACE]<0 then 'S'
when a.[opttype]=''and a.[FACE]<0 then 'S'
else 'B'end 'Sell/Buy'
,case
when a.[Loan/Pool/Ticket Number] in ('1403040003','1401060002') and a.[Portfolio ID]='7' then 'TRS-MSR'
when a.[Loan/Pool/Ticket Number] in ('1401060002','1401070001','1312200001','1402040015','1403040019') and a.[Portfolio ID]='1' then 'PLS-MSR'
when (rtrim(a.[Management]) <>'MSR' or a.[Management]is null)and a.[Portfolio ID]='1' then 'PLS-Pipeline'
when (rtrim(a.[Management]) <>'MSR' or a.[Management]is null) and a.[Portfolio ID]='7' then 'TRS-Pipeline'
when rtrim(a.[Management]) ='MSR' and a.[Portfolio ID]='1' then 'PLS-MSR'
when rtrim(a.[Management]) ='MSR' and a.[Portfolio ID]='7' then 'TRS-MSR'
when a.[Portfolio ID]='2' then 'TRS-Jumbo Acquisition'
when a.[Portfolio ID]='3' then 'MBS Securities'
when a.[Portfolio ID]='4' then 'Jumbo Securities'
end 'Book'
into #All_Trades2
from [qrm_pulsar].dbo.PnL_Tradeshistory a left join #ErrorTrades b
on a.[Loan/Pool/Ticket Number]=b.[Loan/Pool/Ticket Number] and a.[Portfolio ID]=b.[Portfolio ID]
where a.[CreationDt]<=@CurrentDate --and a.[CreationDt]>= @PriorDate
and a.[DataVer]='0' and b.[Loan/Pool/Ticket Number] is null
--Select * from #All_Trades where [CreationDt]='5/16/2014' and [Loan/Pool/Ticket Number]='140429T038'--and [Portfolio ID]='1'
--Select * from #All_Trades2 where [Loan/Pool/Ticket Number] ='140402TY01'
--------------------all trade settlements-------------------------
IF OBJECT_ID('tempdb..#All_Trades_PO') is not null DROP TABLE #All_Trades_PO
select *
,case
when ((rtrim([In Ticket Management])<>'MSR'and rtrim([Out Ticket Management])<>'MSR') or ([In Ticket Management]is null and [Out Ticket Management]is null))and [Portfolio ID]='1' then 'PLS-Pipeline'
when ((rtrim([In Ticket Management])<>'MSR'and rtrim([Out Ticket Management])<>'MSR') or ([In Ticket Management]is null and [Out Ticket Management]is null)) and [Portfolio ID]='7' then 'TRS-Pipeline'
when (rtrim([In Ticket Management]) ='MSR' or rtrim([Out Ticket Management]) ='MSR')and [Portfolio ID]='1' then 'PLS-MSR'
when (rtrim([In Ticket Management]) ='MSR' or rtrim([Out Ticket Management]) ='MSR') and [Portfolio ID]='7' then 'TRS-MSR'
when [Portfolio ID]='2' then 'TRS-Jumbo Acquisition'
when [Portfolio ID]='3' then 'MBS Securities'
when [Portfolio ID]='4' then 'Jumbo Securities'
end 'Book'
into #All_Trades_PO
from [qrm_pulsar].dbo.PnL_TradePairoffshistory
where [CreationDt]=@CurrentDate
and [DataVer]='0'
--Select * from #All_Trades_PO where [In Ticket Number]='1403070007'
--------------------all trade settlements Prior-------------------------
IF OBJECT_ID('tempdb..#All_Trades_PO_Prior') is not null DROP TABLE #All_Trades_PO_Prior
select *
,case
when ((rtrim([In Ticket Management])<>'MSR'and rtrim([Out Ticket Management])<>'MSR') or ([In Ticket Management]is null and [Out Ticket Management]is null))and [Portfolio ID]='1' then 'PLS-Pipeline'
when ((rtrim([In Ticket Management])<>'MSR'and rtrim([Out Ticket Management])<>'MSR') or ([In Ticket Management]is null and [Out Ticket Management]is null)) and [Portfolio ID]='7' then 'TRS-Pipeline'
when (rtrim([In Ticket Management]) ='MSR' or rtrim([Out Ticket Management]) ='MSR')and [Portfolio ID]='1' then 'PLS-MSR'
when (rtrim([In Ticket Management]) ='MSR' or rtrim([Out Ticket Management]) ='MSR') and [Portfolio ID]='7' then 'TRS-MSR'
when [Portfolio ID]='2' then 'TRS-Jumbo Acquisition'
when [Portfolio ID]='3' then 'MBS Securities'
when [Portfolio ID]='4' then 'Jumbo Securities'
end 'Book'
into #All_Trades_PO_Prior
from [qrm_pulsar].dbo.PnL_TradePairoffshistory
where [CreationDt]=@PriorDate
and [DataVer]='0'
---------------------Open Position TL---------------------
IF OBJECT_ID('tempdb..#OPTL') is not null DROP TABLE #OPTL
select
[CreationDt],
[DataVer],
[Company],
[Type],
[Sell/Buy],
[OptType],
cast([Loan/Pool/Ticket Number]as varchar)'Ticket #',
[FACE],
abs([FACE])'absFACE',
[Initial Value],
[Current Value],
[Inception Profit/Loss],
[Instrument Name],
[Trade Date],
[Trade Price],
[Counterparty Name],
[Cost],
[Coupon],
[Settle],
[Portfolio ID],
[Management],
[Book],
[Forward Units]
into #OPTL
from #All_Trades a
where
[dataver]='0'
and [CreationDt]=@CurrentDate
Order
by [Loan/Pool/Ticket Number]
--Select * from #OPTL where [Ticket #]in ('1405200007')
---------------------------------All Trades Beg vs. End ---------------------
IF OBJECT_ID('tempdb..#TL') is not null DROP TABLE #TL
Select
a.[CreationDt]
,a.[DataVer]
,a.[Company]
,a.[Type]
,a.[Sell/Buy],
case when a.[CreationDt]=@PriorDate then 'Beg' else 'Additions' end 'Flag'
,case when c.[Ticket #] IS not null then 'Open' else 'Settled' end 'Settled/Open'
,a.[OptType]
,a.[Loan/Pool/Ticket Number]'Ticket #'
,(a.[FACE])'Beg Face'
,(c.[FACE])'End Face'
,abs(a.[FACE])'abs Beg Face'
,abs(c.[FACE])'abs End Face'
,a.[Initial Value]'Beg Initial Value'
,a.[Current Value]'Beg Current Value'
,a.[Inception Profit/Loss]'Beg P&L'
,c.[Initial Value]'End Initial Value'
,c.[Current Value]'End Current Value'
,c.[Inception Profit/Loss]'End P&L'
,a.[Instrument Name]
,a.[Trade Date]
,a.[Settle]
,a.[Trade Price]
,a.[Counterparty Name]
,a.[Coupon]
,a.[Portfolio ID],
a.[Management]
,a.[Book]
,a.[Forward Units]
into #TL
from
#All_Trades a left join #OPTL c on a.[Loan/Pool/Ticket Number]=c.[Ticket #]and a.[Book]=c.[Book]
where
( a.[DataVer]='0'
and a.[CreationDt] in (Select MIN(CreationDt)from [qrm_pulsar].dbo.PnL_Tradeshistory b
where a.[Loan/Pool/Ticket Number]=b.[Loan/Pool/Ticket Number]
and a.[Portfolio ID]=b.[Portfolio ID]
and [CreationDt]>= @PriorDate
and [CreationDt]<= @CurrentDate
and [DataVer]='0'))
--select * from #TL where [Ticket #]='140529TYM2' [Type] is null --[Type] is null
--select * from #All_Trades where [Loan/Pool/Ticket Number]='140529TYM2' and [Portfolio ID]='1' order by CreationDt--[Type] is null
--select * from #OPTL where [Ticket #]='140307TY01' and [Portfolio ID]='1'--[Type] is null
--select * from #TL where [Type] is null [Portfolio ID]='3'and [Portfolio ID]='1'
-----------------------------Current Trade Settlement TL--------------------
IF OBJECT_ID('tempdb..#STL') is not null DROP TABLE #STL
select *
into #STL
from #All_Trades_PO a
where
[dataver]='0'
and
a.[CreationDt] in (select Max(b.[CreationDt]) from [qrm_pulsar].dbo.PnL_TradePairoffshistory b
where a.[In Ticket Number]= b.[In Ticket Number]
and a.[Out Ticket Number]= b.[Out Ticket Number]
and a.[Portfolio ID]=b.[Portfolio ID]
and [CreationDt]>@PriorDate
and [CreationDt]<=@CurrentDate
and [dataver]='0')
--Select * from #STL where [In Ticket Number] ='1404040002'
-----------------------------Trade Settlement AG join on out ticket number--------------------
IF OBJECT_ID('tempdb..#S1') is not null DROP TABLE #S1
Select
distinct
a.[CreationDt],
a.[DataVer],
a.[Book],
a.[pairoffBuySell]as 'Sell/Buy',
b.[Type],
'Additions' as 'Flag',
'Settled' as 'Settled/Open',
case when a.[OPTION_TYP]='C' then 'Call'
when a.[OPTION_TYP]='P' then 'Put'
else ' ' end 'OptType'
As well as just an END statement at the end of the stored procedure.
Upvotes: 0
Views: 1546
Answers (2)
Rob Farley
Reputation: 15849
Check the settings the stored procedure was created with, such as how NULLs are handled. If you script the database out, you will see these at the top of the script. Then check to see if you have those same settings set when you run the 'Giant SQL Query(tm)'.
Upvotes: 1
Jeremy
Reputation: 4838
The SP and query are pointed to different databases. The query is hard coded to point to the qrm_pulsar database, but you are calling the stored procedure in the qrm_pulsar_sandbox database.
Upvotes: 1
Related Questions
- Stored procedure only executes correctly on first execution
- Executing a stored procedure doesn't complete the whole logic inside it
- Stored Procedure works but not when called with ExecuteNonQuery
- SQL Server: stored procedure not working, but works outside of stored procedure
- Stored procedure fails, but works correctly when it runs manually
- Executing stored procedure not working but individual statements working in stored procedure
- Stored Procedure not execute as expected
- Why does query execute on its own but not in a stored procedure
- Microsoft sql stored procedure not executing select statement properly
- Stored procedure not being executed within another stored procedure