Creating a Stored Procedure from 3 queries

[Satvic]

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

GO

[John Bell]

Hi

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" <fu**@spammers.com> wrote in message
news:bl**********@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

[Erland Sommarskog]

Satvic (fu**@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, so****@algonet.se

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