Creating a Stored Procedure from 3 queries

**John Bell** · Jul 20 '05, 01:37 AM

Re: Creating a Stored Procedure from 3 queries

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" <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_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.Stage
NameID
WHERE m.ManuscriptID = @ManuscriptID
ORDER BY ms.DatePosted DESC

GO

**Erland Sommarskog** · Jul 20 '05, 01:37 AM

Re: Creating a Stored Procedure from 3 queries

Satvic (fuck@spammers. com) writes:[color=blue]
> CREATE PROCEDURE csp_AuthorAccou ntInfo
> @CandidateID int,
>
> AS
>
> DECLARE @ScriptsNo int, @ManuscriptID int
>
> SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
> CandidateID = @CandidateID[/color]

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

CREATE PROCEDURE csp_AuthorAccou ntInfo @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.
[color=blue]
> /* 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[/color]

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/techinf...2000/books.asp