468,720 Members | 1,873 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,720 developers. It's quick & easy.

SELECT returning multiple values in a stored proc

Hi

I'm not sure what the best approach for this is:

I have a stored procedure which I would like to use to return several
output values instead of returning a recordset.

CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int
OUTPUT) AS
SELECT field2, field3 FROM Table WHERE field1 = @param1
I would like to return @param2 as field2 and @param3 as field3

How do I do this without using SELECT multiple times?

THanks in advance

Sam
Jul 20 '05 #1
6 41879


Samuel Hon wrote:
Hi

I'm not sure what the best approach for this is:

I have a stored procedure which I would like to use to return several
output values instead of returning a recordset.

CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int
OUTPUT) AS
SELECT field2, field3 FROM Table WHERE field1 = @param1

I would like to return @param2 as field2 and @param3 as field3

How do I do this without using SELECT multiple times?

THanks in advance

Sam


Did you try:

CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int OUTPUT)
AS SELECT @param2 = field2, @param3 = field3 FROM Table WHERE field1 = @param1

Joe
Jul 20 '05 #2
Samuel Hon (no*****@samuelhon.co.uk) writes:
I'm not sure what the best approach for this is:

I have a stored procedure which I would like to use to return several
output values instead of returning a recordset.

CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int
OUTPUT) AS
SELECT field2, field3 FROM Table WHERE field1 = @param1
I would like to return @param2 as field2 and @param3 as field3

How do I do this without using SELECT multiple times?


SELECT @param2 = field2, @param3 = field4 FROM tbl WHERE field = @param1

Note that this is only useful, if you know or can assume that the
SELECT returns only one row. If it returns more than one row, receiving
the data in output parameters is not a good idea. In that case you should
use a result set.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Hi

I tried that and got this message

"A SELECT statement that assigns a value to a variable must not be
combined with data-retrieval operations"

I must be doing something stupid

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
Samuel Hon (no*****@samuelhon.co.uk) writes:
I'm not sure what the best approach for this is:

I have a stored procedure which I would like to use to return several
output values instead of returning a recordset.

CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int
OUTPUT) AS
SELECT field2, field3 FROM Table WHERE field1 = @param1
I would like to return @param2 as field2 and @param3 as field3

How do I do this without using SELECT multiple times?


SELECT @param2 = field2, @param3 = field4 FROM tbl WHERE field = @param1

Note that this is only useful, if you know or can assume that the
SELECT returns only one row. If it returns more than one row, receiving
the data in output parameters is not a good idea. In that case you should
use a result set.

Jul 20 '05 #4
It seems that you have to assign all the values to parameters so I
cant do

SELECT @param2 = field2, field3 FROM tbl WHERE field = @param1

I have to do

SELECT @param2 = field2, @param3 = field3 FROM tbl WHERE field =
@param1

I dont need a recordset, just testing for existence of a record so the
where clause is a little more complex than I've shown

Thanks for the help

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
Samuel Hon (no*****@samuelhon.co.uk) writes:
I'm not sure what the best approach for this is:

I have a stored procedure which I would like to use to return several
output values instead of returning a recordset.

CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int
OUTPUT) AS
SELECT field2, field3 FROM Table WHERE field1 = @param1
I would like to return @param2 as field2 and @param3 as field3

How do I do this without using SELECT multiple times?


SELECT @param2 = field2, @param3 = field4 FROM tbl WHERE field = @param1

Note that this is only useful, if you know or can assume that the
SELECT returns only one row. If it returns more than one row, receiving
the data in output parameters is not a good idea. In that case you should
use a result set.

Jul 20 '05 #5
Samuel Hon (no*****@samuelhon.co.uk) writes:
SELECT @param2 = field2, @param3 = field3 FROM tbl WHERE field =
@param1

I dont need a recordset, just testing for existence of a record so the
where clause is a little more complex than I've shown


In such case you should do:

CREATE PROCEDURE does_it_exist_sp @inputpar some_type,
@exists bit OUTPUT

SELECT @exists = CASE WHEN EXISTS (SELECT *
FROM tbl
WHERE col = @inputpar=
THEN 1
ELSE 0
END
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
Thanks Erland

I'm returning a few fields about the exising record, hence the output params
CREATE PROCEDURE does_it_exist_sp @inputpar some_type,
@exists bit OUTPUT

SELECT @exists = CASE WHEN EXISTS (SELECT *
FROM tbl
WHERE col = @inputpar=
THEN 1
ELSE 0
END

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Stanley Sinclair | last post: by
5 posts views Thread by Paul Aspinall | last post: by
1 post views Thread by Ahmet Karaca | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Oskars | last post: by
9 posts views Thread by bryonone | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.