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
