Creating a Stored Procedure from 3 queries

Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.StageNameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GOHi

A few points:

Check out http://www.aspfaq.com/show.asp?id=2319 for multiple record sets.

All three queries look like they should be combined into one, if not, you
will need to use a cursor.

Your second query is trying to set variables and also return a result set,
which is not allowed.

It is not good to use select * in production code.

I think you should also check for errors and add a SET NOCOUNT ON.

John

"Satvic" <fuck@.spammers.com> wrote in message
news:bl64ub$6de$1@.ins22.netins.net...
Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times
DB gets access from 1 asp page. The result procedure only works 1/2 way
(does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the
number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's
info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted
manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted,
ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID =
ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.Stage
NameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GO|||Satvic (fuck@.spammers.com) writes:
> CREATE PROCEDURE csp_AuthorAccountInfo
> @.CandidateID int,
> AS
> DECLARE @.ScriptsNo int, @.ManuscriptID int
> SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
> CandidateID = @.CandidateID

It would probably be better to return the count as an output parameter:

CREATE PROCEDURE csp_AuthorAccountInfo @.CandidateID int,
@.ScriptsNo int OUTPUT AS

DECLARE @.ManuscriptID int

SELECT @.ScriptsNo = count(*) as ScriptsNo
FROM Manuscripts WITH (NOLOCK)
WHERE CandidateID = @.CandidateID

And don't use NOLOCK unless you understand exactly what you are doing.

> /* this is where it stops all the time :(
> Theoretically speaking, next SELECT will only return 1 row with
> Candidate's info*/
> SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State,
> cn.Country
> FROM Candidates c INNER JOIN
> Manuscripts m ON
> c.CandidateID = m.CandidateID INNER JOIN
> Locations l ON
> c.LocationID = l.LocationID INNER JOIN
> cn ON
> l.CountryCode = cn.CountryCode
> WHERE c.CandidateID = @.CandidateID

As John Bell pointed out, you cannot assigned variables and return
result sets in the same query.

And your comment that the SELECT would only return one seems a bit funny.
I don't know your tables, but if this row returns 1 row, then the
SELECT COUNT(*) always returns 1.

I don't know about your tables, but I would guess that a Candidate can
zero or more Manuscripts, in which case the above could return 0 or
more rows.

Since I don't know the data you are accessing, it is difficult to give
precise advice, but you should probably investigate the use of temp
tables and table variables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp