December 22, 2008 at 3:48 pm
I wrote a sp that only gets the first 6 lines of a query by using "TOP 6 WITH TIES"...sometimes, other times just one row depending on a variable.
I needed to get those for each product type, so I wrote another sp that opens a cursor and then calls the first sp. This all works fine but Reporting services only returns the first result set while in SQL Server Managemnt Studio I get result sets for all the product types. Is there a way to collect the result sets and only return one result set with all the data from calling the outer sp?
outer sp:
ALTER PROCEDURE [dbo].[sp_ProfitStar]
@date datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RCI int
DECLARE @ProdID int
DECLARE @ProdType int
DECLARE product_cursor CURSOR FOR
SELECT ProdID, ProductType
FROM Product
where ProdCloseDate > @date and
ProdOpenDate <= @date
OPEN product_cursor
FETCH NEXT FROM product_cursor
INTO @ProdID, @ProdType
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE @RCI = [Rates].[dbo].[sp_ProfitStatEX]
@Date
,@ProdID
,@ProdType
FETCH NEXT FROM product_cursor
INTO @ProdID, @ProdType
END
CLOSE product_cursor
DEALLOCATE product_cursor
END
Thanks,
DaveK
December 22, 2008 at 4:45 pm
Can you show us the "sp_ProfitStatEX" procedure code please?
You cannot just generate 6 result sets and expect the client to treat them all as one result set. You have to gether them together yourself into a single result set that you return to the client.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 9:04 am
Not that it's really relevant, it could have been a simple select statement and it would behave the same way, but OK, here you go...
USE [Rates]
GO
/****** Object: StoredProcedure [dbo].[sp_ProfitStatEX] Script Date: 12/23/2008 08:04:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ProfitStatEX]
@date datetime,
@ProdID int,
@ProdType int
AS
BEGIN
SET NOCOUNT ON;
Declare @AsOfDate as varchar(100)
Declare @pid as varchar(100)
set @pid = convert(varchar,@ProdID)
Declare @SQL_Statement as varchar(8000)
Set @AsOfDate = '''' + Cast(Month(@Date) as varchar) +'/' + Cast(Day(@Date) as varchar) + '/' + cast(Year(@Date) as varchar) + ''''
set @SQL_Statement =
(CASE @ProdType
WHEN 2 THEN
'select TOP 6 WITH TIES P.ProdNameToPrint,
P.ProductKey,
P.ProdID,
P.InNotesProfileID,
P.ProdAutoPayDisc,
P.ProductType,
P.ProdAutoPayDiscAmt,
M.Underwriting,
M.MarginPymPeriod, M.Grade, G.BeaconRange, G.PaperGrade, isnull(M.MarginValue,0) as MarginValue,
M.LTV, L.LTVDesc, L.LTVOrder, isnull(IV.IndexValue,0) as IndexValue, B.IndexType,
isnull(B.BaseRate,0) as BaseRate from Product AS P
LEFT join RatesFeatures AS RF on P.ProductKey=RF.ProductKey and RF.EndDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), RF.StartDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT join BaseRate AS B on P.BaseRateID=B.BaserateID and B.ExpirationDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), B.EffectiveDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT JOIN IndexValue as IV ON IV.IndexType = B.IndexType AND IV.IndexExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), IV.IndexEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Margin AS M on M.BaseRateID=B.BaseRateID and M.MarginExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), M.MarginEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Grade AS G on G.Grade=M.Grade
left join LTV AS L on M.LTV=L.LTV
where P.ProdID=convert(int, '+@pid+') and P.ProductType=2 and P.ProdCloseDate> ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), P.ProdOpenDate, 101) AS DATETIME) <= ' + @AsOfDate +
' ORDER BY P.ProductKey desc, M.MarginPymPeriod, L.LTVOrder, M.Grade'
ELSE
'select TOP 1 WITH TIES P.ProdName, P.ProdNameToPrint,
P.ProdID, P.ProductKey,
P.InNotesProfileID,
P.ProdAutoPayDisc,
P.ProductType,
P.ProdAutoPayDiscAmt,
P.ProdMinOpenBal,
P.ProdMinReqBal,
P.ProdBalMethod,
P.ProdServiceCharge,
M.MarginPymPeriod, M.Grade, G.BeaconRange, G.PaperGrade, ISNULL(M.MarginValue,0) as MarginValue, L.LTVDesc, L.LTVOrder, ISNULL(IV.IndexValue,0) as IndexValue, B.IndexType,
B.BaseRate,
isnull(RF.CompoundingPeriod,12) as CompoundingPeriod
from Product AS P
LEFT join RatesFeatures AS RF on P.ProductKey=RF.ProductKey and RF.EndDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), RF.StartDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT join BaseRate AS B on P.BaseRateID=B.BaserateID and B.ExpirationDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), B.EffectiveDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT JOIN IndexValue as IV ON IV.IndexType = B.IndexType AND IV.IndexExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), IV.IndexEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Margin AS M on M.BaseRateID=B.BaseRateID and M.MarginExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), M.MarginEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Grade AS G on G.Grade=M.Grade
left join LTV AS L on M.LTV=L.LTV
where P.ProdID=convert(int, '+@pid+') and P.ProductType=1 and P.ProdCloseDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), P.ProdOpenDate, 101) AS DATETIME) <= ' + @AsOfDate +
' ORDER BY P.ProductKey desc, M.MarginPymPeriod, L.LTVOrder, M.Grade'
END)
EXEC(@SQL_Statement)
END
December 23, 2008 at 9:25 am
Hmm, did you realize that based on ProductType, the fields (columns) that are returned are substantially different? How would you see that being resolved?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 9:31 am
It's in development. I took the queries from two different reports that the user wants to see on the same report. Details, details 😀
I was going to take care of later. But thanks for noticing.
December 23, 2008 at 9:38 am
I think I will nickname it the 'Apples and Oranges' report because that's what they want to see on the same report. Some rates are calculated one way and some rates are calculated another way and they really don't fit on the same report but I try to please.
December 23, 2008 at 9:43 am
Yes, but Reporting Services is expecting a single consistent set of columns in the rows that are returned to it. Are you saying that it doesn't matter which column set I use at this point?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 10:16 am
They are both the same now:
USE [Rates]
GO
/****** Object: StoredProcedure [dbo].[sp_ProfitStatEX] Script Date: 12/23/2008 08:04:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ProfitStatEX]
@date datetime,
@ProdID int,
@ProdType int
AS
BEGIN
SET NOCOUNT ON;
Declare @AsOfDate as varchar(100)
Declare @pid as varchar(100)
set @pid = convert(varchar,@ProdID)
Declare @SQL_Statement as varchar(8000)
Set @AsOfDate = '''' + Cast(Month(@Date) as varchar) +'/' + Cast(Day(@Date) as varchar) + '/' + cast(Year(@Date) as varchar) + ''''
set @SQL_Statement =
(CASE @ProdType
WHEN 2 THEN
'select TOP 6 WITH TIES P.ProdNameToPrint,
P.ProductKey,
P.ProdID,
P.ProductType,
P.ProdAutoPayDiscAmt,
M.Underwriting,
M.MarginPymPeriod,
P.ProdMinOpenBal,
P.ProdMinReqBal,
P.ProdBalMethod,
P.ProdServiceCharge,
M.MarginPymPeriod,
M.Grade,
G.BeaconRange,
G.PaperGrade,
isnull(M.MarginValue,0) as MarginValue,
M.LTV,
L.LTVDesc,
L.LTVOrder,
isnull(IV.IndexValue,0) as IndexValue,
B.IndexType,
isnull(B.BaseRate,0) as BaseRate,
isnull(RF.CompoundingPeriod,12) as CompoundingPeriod
from Product AS P
LEFT join RatesFeatures AS RF on P.ProductKey=RF.ProductKey and RF.EndDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), RF.StartDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT join BaseRate AS B on P.BaseRateID=B.BaserateID and B.ExpirationDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), B.EffectiveDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT JOIN IndexValue as IV ON IV.IndexType = B.IndexType AND IV.IndexExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), IV.IndexEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Margin AS M on M.BaseRateID=B.BaseRateID and M.MarginExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), M.MarginEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Grade AS G on G.Grade=M.Grade
left join LTV AS L on M.LTV=L.LTV
where P.ProdID=convert(int, '+@pid+') and P.ProductType=2 and P.ProdCloseDate> ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), P.ProdOpenDate, 101) AS DATETIME) <= ' + @AsOfDate +
' ORDER BY P.ProductKey desc, M.MarginPymPeriod, L.LTVOrder, M.Grade'
ELSE
'select TOP 1 WITH TIES P.ProdNameToPrint,
P.ProductKey,
P.ProdID,
P.ProductType,
P.ProdAutoPayDiscAmt,
M.Underwriting,
M.MarginPymPeriod,
P.ProdMinOpenBal,
P.ProdMinReqBal,
P.ProdBalMethod,
P.ProdServiceCharge,
M.MarginPymPeriod,
M.Grade,
G.BeaconRange,
G.PaperGrade,
ISNULL(M.MarginValue,0) as MarginValue,
M.LTV,
L.LTVDesc,
L.LTVOrder,
ISNULL(IV.IndexValue,0) as IndexValue,
B.IndexType,
isnull(B.BaseRate,0) as BaseRate,
isnull(RF.CompoundingPeriod,12) as CompoundingPeriod
from Product AS P
LEFT join RatesFeatures AS RF on P.ProductKey=RF.ProductKey and RF.EndDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), RF.StartDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT join BaseRate AS B on P.BaseRateID=B.BaserateID and B.ExpirationDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), B.EffectiveDate, 101) AS DATETIME) <= ' + @AsOfDate +
' LEFT JOIN IndexValue as IV ON IV.IndexType = B.IndexType AND IV.IndexExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), IV.IndexEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Margin AS M on M.BaseRateID=B.BaseRateID and M.MarginExpDate > ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), M.MarginEffDate, 101) AS DATETIME) <= ' + @AsOfDate +
' left join Grade AS G on G.Grade=M.Grade
left join LTV AS L on M.LTV=L.LTV
where P.ProdID=convert(int, '+@pid+') and P.ProductType=1 and P.ProdCloseDate> ' + @AsOfDate +
' and CAST(CONVERT(VARCHAR(20), P.ProdOpenDate, 101) AS DATETIME) <= ' + @AsOfDate +
' ORDER BY P.ProductKey desc, M.MarginPymPeriod, L.LTVOrder, M.Grade'
END)
EXEC(@SQL_Statement)
END
December 23, 2008 at 10:57 am
Why are all of these dates being converted to VARCHAR and then back to DATETIME again? Does this accomplish anything?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 11:13 am
Well, it seemed like the dynamic SQL and all of these conversions back and forth were unnecessary so I did without them. Anyway, this is how I would do it:
ALTER PROCEDURE [dbo].[sp_ProfitStar] @date datetime AS
SET NOCOUNT ON;
SELECT
P.ProdNameToPrint,
P.ProductKey,
P.ProdID,
P.ProductType,
P.ProdAutoPayDiscAmt,
M.Underwriting,
M.MarginPymPeriod,
P.ProdMinOpenBal,
P.ProdMinReqBal,
P.ProdBalMethod,
P.ProdServiceCharge,
M.MarginPymPeriod,
M.Grade,
G.BeaconRange,
G.PaperGrade,
isnull(M.MarginValue,0) as MarginValue,
M.LTV,
L.LTVDesc,
L.LTVOrder,
isnull(IV.IndexValue,0) as IndexValue,
B.IndexType,
isnull(B.BaseRate,0) as BaseRate,
isnull(RF.CompoundingPeriod,12) as CompoundingPeriod,
RANK() Over (Partition By P.ProductID
Order By P.ProductKey desc, M.MarginPymPeriod, L.LTVOrder, M.Grade) as KeyRank
FROM Product
LEFT join RatesFeatures AS RF on P.ProductKey=RF.ProductKey
and RF.EndDate > @date
and RF.StartDate <= @date
LEFT join BaseRate AS B on P.BaseRateID=B.BaserateID
and B.ExpirationDate > @date
and B.EffectiveDate <= @date
LEFT JOIN IndexValue as IV ON IV.IndexType = B.IndexType
AND IV.IndexExpDate > @date
and IV.IndexEffDate <= @date
LEFT JOIN Margin AS M on M.BaseRateID=B.BaseRateID
and M.MarginExpDate > @date
and M.MarginEffDate <= @date
LEFT JOIN Grade AS G on G.Grade=M.Grade
LEFT JOIN LTV AS L on M.LTV=L.LTV
Where ProdCloseDate > @date
And ProdOpenDate <= @date
AND ((P.ProductType = 2 And KeyRank <= 6)
Or (P.ProductType = 1 And KeyRank <= 1))
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 12:27 pm
The dates are being converted to chop off the time because our visual PC guys make the effective dates effective at 3PM, 10AM, 5:37PM or whenever the user did the change, of a given date and the date should be effective at midnight.
9/10/2008 2:45:29 PM is not less than or equal to 9/10/2008 00:00:00
December 23, 2008 at 1:32 pm
In that case, this may serve you better:
ALTER PROCEDURE [dbo].[sp_ProfitStar] @date datetime AS
SET NOCOUNT ON;
Declare @DayBegin datetime, @DayNext as datetime
Select @DayBegin = DateAdd(dd, DateDiff(dd, 0, @date), 0)
, @DayNext = DateAdd(dd, DateDiff(dd, 0, @date), 1)
SELECT
P.ProdNameToPrint,
P.ProductKey,
P.ProdID,
P.ProductType,
P.ProdAutoPayDiscAmt,
M.Underwriting,
M.MarginPymPeriod,
P.ProdMinOpenBal,
P.ProdMinReqBal,
P.ProdBalMethod,
P.ProdServiceCharge,
M.MarginPymPeriod,
M.Grade,
G.BeaconRange,
G.PaperGrade,
isnull(M.MarginValue,0) as MarginValue,
M.LTV,
L.LTVDesc,
L.LTVOrder,
isnull(IV.IndexValue,0) as IndexValue,
B.IndexType,
isnull(B.BaseRate,0) as BaseRate,
isnull(RF.CompoundingPeriod,12) as CompoundingPeriod,
RANK() Over (Partition By P.ProductID
Order By P.ProductKey desc, M.MarginPymPeriod, L.LTVOrder, M.Grade) as KeyRank
FROM Product
LEFT join RatesFeatures AS RF on P.ProductKey=RF.ProductKey
and RF.EndDate > @DayBegin
and RF.StartDate < @DayNext
LEFT join BaseRate AS B on P.BaseRateID=B.BaserateID
and B.ExpirationDate > @DayBegin
and B.EffectiveDate < @DayNext
LEFT JOIN IndexValue as IV ON IV.IndexType = B.IndexType
AND IV.IndexExpDate > @DayBegin
and IV.IndexEffDate < @DayNext
LEFT JOIN Margin AS M on M.BaseRateID=B.BaseRateID
and M.MarginExpDate > @DayBegin
and M.MarginEffDate < @DayNext
LEFT JOIN Grade AS G on G.Grade=M.Grade
LEFT JOIN LTV AS L on M.LTV=L.LTV
Where ProdCloseDate > @DayBegin
And ProdOpenDate < @DayNext
AND ((P.ProductType = 2 And KeyRank <= 6)
Or (P.ProductType = 1 And KeyRank <= 1))
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 2:53 pm
I am getting 'RANK' is not a recognized function name.
I figured it out though:
outer sp
drop and create #temp table
open cursor
fetch cursor
loop
EXEC inner sp
fetch cursor
end loop
close cursor
select * from #temp table
end
inner sp
blah blah blah
:
:
(CASE @ProdType
WHEN 2 THEN
insert into #temp table
select TOP 6 WITH TIES ...
ELSE
insert into #temp table
select TOP 1 WITH TIES ...
end
December 23, 2008 at 3:21 pm
David Kingman (12/23/2008)
I am getting 'RANK' is not a recognized function name.
Ah. Let me guess: you are running on SQL Server 2000? (or else your DB compatibility level is set to 80)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 24, 2008 at 10:17 am
Yes, the database is on a SQL Server 2000 instance.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply