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]

Re: SELECT returning multiple values in a stored proc



Samuel Hon wrote:
[color=blue]
> 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[/color]

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]

Re: SELECT returning multiple values in a stored proc

Samuel Hon (noreply@samuel hon.co.uk) writes:[color=blue]
> 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?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Samuel Hon]

Re: SELECT returning multiple values in a stored proc

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 <sommar@algonet .se> wrote in message news:<Xns941019 43E7ACYazorman@ 127.0.0.1>...[color=blue]
> Samuel Hon (noreply@samuel hon.co.uk) writes:[color=green]
> > 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?[/color]
>
> 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.[/color]

[Samuel Hon]

Re: SELECT returning multiple values in a stored proc

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 <sommar@algonet .se> wrote in message news:<Xns941019 43E7ACYazorman@ 127.0.0.1>...[color=blue]
> Samuel Hon (noreply@samuel hon.co.uk) writes:[color=green]
> > 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?[/color]
>
> 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.[/color]

[Erland Sommarskog]

Re: SELECT returning multiple values in a stored proc

Samuel Hon (noreply@samuel hon.co.uk) writes:[color=blue]
> 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[/color]

In such case you should do:

CREATE PROCEDURE does_it_exist_s p @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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Samuel Hon]

Re: SELECT returning multiple values in a stored proc

Thanks Erland

I'm returning a few fields about the exising record, hence the output params
[color=blue]
> CREATE PROCEDURE does_it_exist_s p @inputpar some_type,
> @exists bit OUTPUT
>
> SELECT @exists = CASE WHEN EXISTS (SELECT *
> FROM tbl
> WHERE col = @inputpar=
> THEN 1
> ELSE 0
> END[/color]