SELECT returning multiple values in a stored proc

[Samuel Hon]

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

[Joseph Weinstein]

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

[Erland Sommarskog]

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

[Samuel Hon]

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.

[Samuel Hon]

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.

[Erland Sommarskog]

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

[Samuel Hon]

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