Calling SP2 from SP1

I have 3 reports, and all 3 of them have an option that returns the
same information. So, I created a stored procedure to pull that info.
Now, I want to call that SP from another SP. Both will return the same
fields for the report.
I'm thinking what I need is probably very simple, but don't know what
it might be. Below is what I have so far:
THANKS!
SP1 ---
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name =
'spDATAControlReport1')
BEGIN
PRINT 'Dropping procedure spDATAControlReport1'
DROP PROCEDURE dbo.spDATAControlReport1
END
GO
PRINT 'Creating procedure spDATAControlReport1'
GO
CREATE PROCEDURE dbo.spDATAControlReport1
(
@.CaseNbrMin varchar(12),
@.CaseNbrMax varchar(12),
@.StartDt datetime,
@.EndDt datetime,
@.ReportType int
)
AS
BEGIN
-- New Filings
IF @.ReportType = 1
BEGIN
CREATE TABLE FilingsTable (col1 varchar(12), col2 varchar(12), col3
varchar(100), col4 varchar(100))
INSERT INTO FilingsTable EXECUTE dbo.spNewFilingsRpt @.CaseNbrMin,
@.CaseNbrMax, @.StartDt, @.EndDt, 1
END
ELSE
SELECT DISTINCT cs.CaseNbr, dbo.fnJisCourtName(cs.CourtID) AS
CourtName, cs.CourtID, cs.Plaintiff, cs.Defendant
FROM CaseSummary cs
INNER JOIN ItsCaseDocuments icd ON cs.CaseNbr = icd.CaseNbr
AND cs.CDI = icd.CDI
WHERE (icd.CaseNbr NOT IN (SELECT CaseNbr FROM ItsCaseDocuments WHERE
DocumentCode LIKE '%D2468%'))
AND (cs.CaseNbr >= @.CaseNbrMin AND cs.CaseNbr <= @.CaseNbrMax)
AND (cs.FileDt BETWEEN @.StartDt AND @.EndDt)
ORDER BY cs.CaseNbr
END
GO
GRANT EXEC ON dbo.spDATAControlReport1 TO PUBLIC
GO
SP2----
--
Then, the stored procedure being called above is:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name =
'spNewFilingsRpt')
BEGIN
PRINT 'Dropping procedure spNewFilingsRpt'
DROP PROCEDURE dbo.spNewFilingsRpt
END
GO
PRINT 'Creating procedure spNewFilingsRpt'
GO
CREATE PROCEDURE dbo.spNewFilingsRpt
(
@.CaseNbrMin varchar(12),
@.CaseNbrMax varchar(12),
@.StartDt datetime,
@.EndDt datetime,
@.ReportType int
)
AS
BEGIN
-- Case 1: 10-19 characters - look at chars 6-10
-- Case 2: 20+ characters - look at chars 6-10 AND 16-20
SELECT DISTINCT cs.CaseNbr, cs.CourtID, cs.Plaintiff, cs.Defendant,
ict.Attribute1
FROM ItsImageIndexDetail iid
INNER JOIN ItsCodeTable ict ON (LEN(iid.CaseFileType) >=10 AND
SUBSTRING(iid.CaseFileType, 6, 5) = ict.Code)
OR (LEN(iid.CaseFileType) >=20 AND SUBSTRING(iid.CaseFileType, 16, 5)
= ict.Code)
INNER JOIN CaseSummary cs ON iid.CaseNbr = cs.CaseNbr
AND iid.CDI = cs.CDI
WHERE (cs.CaseNbr >= @.CaseNbrMin AND cs.CaseNbr <= @.CaseNbrMax)
AND (cs.FileDt >= @.StartDt AND cs.FileDt <= @.EndDt)
AND @.ReportType = 1
ORDER BY cs.CaseNbr
END
GO
GRANT EXEC ON dbo.spNewFilingsRpt TO PUBLIC
GOHi
IF @.ReportType = 1 you are not returning a result set, just populating the
fillings table.
It will probably be best to use two separate stored procedures each stored
procedure returns the appropriate results.
e.g.
CREATE PROCEDURE dbo.spDATAControlReport1 @.CaseNbrMin varchar(12),
@.CaseNbrMax varchar(12),
@.StartDt datetime,
@.EndDt datetime,
@.ReportType int
AS
SET NOCOUNT ON
DECLARE @.stat int
IF @.ReportType = 1
EXEC @.stat = dbo.spNewFilingsRpt @.CaseNbrMin, @.CaseNbrMax,
@.StartDt,
@.EndDt ,
@.ReportType
ELSE
EXEC @.stat = dbo.spNewFilingsRpt2 @.CaseNbrMin, @.CaseNbrMax,
@.StartDt,
@.EndDt ,
@.ReportType
RETURN @.stat
CREATE PROCEDURE spNewFilingsRpt2 @.CaseNbrMin varchar(12),
@.CaseNbrMax
varchar(12),
@.StartDt
datetime,
@.EndDt
datetime,
@.ReportType
int
AS
SELECT DISTINCT cs.CaseNbr,
dbo.fnJisCourtName(cs.CourtID) AS
CourtName, cs.CourtID, cs.Plaintiff,
cs.Defendant
FROM dbo.CaseSummary cs
JOIN dbo.ItsCaseDocuments icd ON cs.CaseNbr = icd.CaseNbr AND cs.CDI =
icd.CDI
WHERE icd.CaseNbr NOT IN (SELECT CaseNbr FROM dbo.ItsCaseDocuments
WHERE DocumentCode LIKE
'%D2468%')
AND cs.CaseNbr >= @.CaseNbrMin AND cs.CaseNbr <= @.CaseNbrMax
AND cs.FileDt BETWEEN @.StartDt AND @.EndDt
ORDER BY cs.CaseNbr
RETURN @.@.ERROR
Currently you are not returning the same data values from each query which
if this is being processed by the same report I would expect would be
necessary.
See http://www.sommarskog.se/error-handling-II.html on how to make your
error handling more robust.
John
"Carol" <cschanz@.gmail.com> wrote in message
news:1134578974.573664.260540@.f14g2000cwb.googlegroups.com...
>I have 3 reports, and all 3 of them have an option that returns the
> same information. So, I created a stored procedure to pull that info.
> Now, I want to call that SP from another SP. Both will return the same
> fields for the report.
> I'm thinking what I need is probably very simple, but don't know what
> it might be. Below is what I have so far:
> THANKS!
> SP1 ---
> IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name =
> 'spDATAControlReport1')
> BEGIN
> PRINT 'Dropping procedure spDATAControlReport1'
> DROP PROCEDURE dbo.spDATAControlReport1
> END
> GO
> PRINT 'Creating procedure spDATAControlReport1'
> GO
> CREATE PROCEDURE dbo.spDATAControlReport1
> (
> @.CaseNbrMin varchar(12),
> @.CaseNbrMax varchar(12),
> @.StartDt datetime,
> @.EndDt datetime,
> @.ReportType int
> )
> AS
> BEGIN
> -- New Filings
> IF @.ReportType = 1
> BEGIN
> CREATE TABLE FilingsTable (col1 varchar(12), col2 varchar(12), col3
> varchar(100), col4 varchar(100))
> INSERT INTO FilingsTable EXECUTE dbo.spNewFilingsRpt @.CaseNbrMin,
> @.CaseNbrMax, @.StartDt, @.EndDt, 1
> END
> ELSE
> SELECT DISTINCT cs.CaseNbr, dbo.fnJisCourtName(cs.CourtID) AS
> CourtName, cs.CourtID, cs.Plaintiff, cs.Defendant
> FROM CaseSummary cs
> INNER JOIN ItsCaseDocuments icd ON cs.CaseNbr = icd.CaseNbr
> AND cs.CDI = icd.CDI
> WHERE (icd.CaseNbr NOT IN (SELECT CaseNbr FROM ItsCaseDocuments WHERE
> DocumentCode LIKE '%D2468%'))
> AND (cs.CaseNbr >= @.CaseNbrMin AND cs.CaseNbr <= @.CaseNbrMax)
> AND (cs.FileDt BETWEEN @.StartDt AND @.EndDt)
> ORDER BY cs.CaseNbr
> END
> GO
> GRANT EXEC ON dbo.spDATAControlReport1 TO PUBLIC
> GO
> SP2----
--
> Then, the stored procedure being called above is:
> IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name =
> 'spNewFilingsRpt')
> BEGIN
> PRINT 'Dropping procedure spNewFilingsRpt'
> DROP PROCEDURE dbo.spNewFilingsRpt
> END
> GO
> PRINT 'Creating procedure spNewFilingsRpt'
> GO
> CREATE PROCEDURE dbo.spNewFilingsRpt
> (
> @.CaseNbrMin varchar(12),
> @.CaseNbrMax varchar(12),
> @.StartDt datetime,
> @.EndDt datetime,
> @.ReportType int
> )
> AS
> BEGIN
> -- Case 1: 10-19 characters - look at chars 6-10
> -- Case 2: 20+ characters - look at chars 6-10 AND 16-20
> SELECT DISTINCT cs.CaseNbr, cs.CourtID, cs.Plaintiff, cs.Defendant,
> ict.Attribute1
> FROM ItsImageIndexDetail iid
> INNER JOIN ItsCodeTable ict ON (LEN(iid.CaseFileType) >=10 AND
> SUBSTRING(iid.CaseFileType, 6, 5) = ict.Code)
> OR (LEN(iid.CaseFileType) >=20 AND SUBSTRING(iid.CaseFileType, 16, 5)
> = ict.Code)
> INNER JOIN CaseSummary cs ON iid.CaseNbr = cs.CaseNbr
> AND iid.CDI = cs.CDI
> WHERE (cs.CaseNbr >= @.CaseNbrMin AND cs.CaseNbr <= @.CaseNbrMax)
> AND (cs.FileDt >= @.StartDt AND cs.FileDt <= @.EndDt)
> AND @.ReportType = 1
> ORDER BY cs.CaseNbr
> END
> GO
> GRANT EXEC ON dbo.spNewFilingsRpt TO PUBLIC
> GO
>