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_AuthorAccou ntInfo
@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
ManuscriptStage s ms ON m.ManuscriptID = ms.ManuscriptID INNER JOIN
ManuscriptStage Names msn ON ms.StageNameID = msn.StageNameID
WHERE m.ManuscriptID = @ManuscriptID
ORDER BY ms.DatePosted DESC
GO