Stored Procedure Loop Throug Date Range
I am running a stored procedure which returns and execute on a single date but is there a way to loop through the stored procedure to call a date range instead versus I manually entering 1 date at a time? Thanks!
USE [Dev]
--Run the Appraisal proc passing in a few required parameters
Declare @ReportData varbinary(max)
Exec dbo.pAppraisal
@ReportData = @ReportData out,
@Portfolios = '1234567',
@Date = '2014-3-31',
@IncludeUnsupervisedAssets = 1,
@IncludeClosedPortfolios = 1
Select c.PortfolioBaseCode, c.ReportHeading1
, a.SecTypeCode, b.Symbol, b.FullName
, a.MarketValue, vs.IsUnsupervised
From dbo.fAppraisal(@ReportData) a
join dbo.vSec b on a.SecurityID = b.SecurityID
join dbo.vSecVariant vs on vs.SecurityID = a.SecurityID AND vs.IsShort = a.IsShortPosition AND vs.SecTypeCode = a.SecTypeCode
join dbo.vPortBase c on a.PortfolioBaseID = c.PortfolioBaseIDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should be able to wrap it into an SP:
Something like this should work. You'll have to figure out how to return the resultant table. And I'm show how to do it on a thirty day from the input date. But you could also input a start and end date. Either way it should be relatively easy.
CREATE PROCEDURE cp_Build_Appraisals @LastDate as datetime
-- I'm not sure how to get a table back from an SP off hand.
AS
BEGIN
...
-- I would change this chunk to the reporting periods you want
select @RptDate = dateadd(day,-30, @LastDate)
While @RptDate <= @LastDate
....
ENDSomething like this should work. You'll have to figure out how to return the resultant table. And I'm show how to do it on a thirty day from the input date. But you could also input a start and end date. Either way it should be relatively easy.
ASKER
Cool, thank you again Jim!
ASKER