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
>

Calendar control in SP2

I know that the SP2 Beta is around for testing. Does anyone know if in
this version the Calendar control is made available for use for date
parameter input ? I believe the Calendar control is the most needed
control in any kind of reporting. Comments Please.
thanks,
Anand Sagar
IN4VelocityAnand Sagar wrote:
> I know that the SP2 Beta is around for testing. Does anyone know if in
> this version the Calendar control is made available for use for date
> parameter input ? I believe the Calendar control is the most needed
> control in any kind of reporting. Comments Please.
For what?
I use a calendar-control in a normal aspx-page and open the report in my
aspx-page
regards
Frank|||It is not SP2. Look for it in the upcoming version Yukon of SQL Server and
Reporting Services.
--
| From: anandsagar@.gmail.com (Anand Sagar)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Calendar control in SP2
| Date: 27 Jan 2005 01:50:02 -0800
| Organization: http://groups.google.com
| Lines: 8
| Message-ID: <3f76a771.0501270150.17a13aea@.posting.google.com>
| NNTP-Posting-Host: 61.95.202.80
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1106819402 1467 127.0.0.1 (27 Jan 2005
09:50:02 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Thu, 27 Jan 2005 09:50:02 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08|||Oh, and I was so wanting it in SP2 :(
--
Adrian M.
MCP
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:ZHvXhpJBFHA.1092@.cpmsftngxa10.phx.gbl...
> It is not SP2. Look for it in the upcoming version Yukon of SQL Server and
> Reporting Services.
> --
> | From: anandsagar@.gmail.com (Anand Sagar)
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Subject: Calendar control in SP2
> | Date: 27 Jan 2005 01:50:02 -0800
> | Organization: http://groups.google.com
> | Lines: 8
> | Message-ID: <3f76a771.0501270150.17a13aea@.posting.google.com>
> | NNTP-Posting-Host: 61.95.202.80
> | Content-Type: text/plain; charset=ISO-8859-1
> | Content-Transfer-Encoding: 8bit
> | X-Trace: posting.google.com 1106819402 1467 127.0.0.1 (27 Jan 2005
> 09:50:02 GMT)
> | X-Complaints-To: groups-abuse@.google.com
> | NNTP-Posting-Date: Thu, 27 Jan 2005 09:50:02 +0000 (UTC)
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08
> phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news.glorb.com!postnews.goog
> le.com!not-for-mail
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:41015
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I know that the SP2 Beta is around for testing. Does anyone know if in
> | this version the Calendar control is made available for use for date
> | parameter input ? I believe the Calendar control is the most needed
> | control in any kind of reporting. Comments Please.
> |
> | thanks,
> | Anand Sagar
> | IN4Velocity
> |
>