April 24, 2009 at 10:39 am
Hi there,
As subject, some of the articles on the web seem to indicate that stored procedures with output parameters do return only one record. Is that correct or can they return a recordset with more than one single record? Thanks in advance.
Regards,
-Benton
April 24, 2009 at 11:15 am
OUTPUT parameters are not used to return recordsets. SELECT is.
You can combine both in one stored procedure.
Do you have a specific case you would like to discuss or this is just a theoretical question?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 2:00 pm
Take this example for instance:
CREATE PROCEDURE [dbo].[LISTADOCS]
@INTERNOS BIT,
@numero integer output,
@descesp varchar(30) output,
@descing varchar(30) output
AS
BEGIN
SELECT @numero=NUMERO,@descesp=DESCESP,@descing=DESCING
FROM DOCS
WHERE INTERNO=@INTERNOS
END
If I call it like this:
declare @outpar1 int
declare @outpar2 varchar(30)
declare @outpar3 varchar(30)
exec listadocs 3 ,@outpar1 OUT,@outpar2 OUT,@outpar3 OUT
select @outpar1,@outpar2,@outpar3
I get a single record returned. Without using output parameters, the same procedure returns a 6 records recordset.
Am I missing something?
April 24, 2009 at 2:04 pm
As I mentioned before the OUTPUT parameters are not designed to return recordsets. You want your SP to look like this
CREATE PROCEDURE [dbo].[LISTADOCS]
@INTERNOS BIT
AS
SELECT
NUMERO,
DESCESP,
DESCING
FROM DOCS
WHERE INTERNO=@INTERNOS
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 2:19 pm
Yes, when I create the procedure that way it returns a recodset of several records. So in short, the answer to my question is yes, right?
April 24, 2009 at 2:31 pm
yes and no.
The OUTPUT parameters are unrelated values. You can use them for returning data from a single record if you know you are only getting one record at a time.
You can also use them to return unrelated data from different records.
And they are independent from the recordset you can return with your SELECTs.(You can have more then one SELECT as well)
For example
CREATE PROCEDURE [dbo].[LISTADOCS]
@INTERNOS BIT,
@desc1 varchar(30) output,
@desc2 varchar(30) output
AS
SELECT @desc1 = DESCESP
FROM DOCS
WHERE INTERNO = 1
SELECT @desc2 = DESCING
FROM DOCS
WHERE INTERNO = 2
SELECT NUMERO,DESCESP,DESCING
FROM DOCS
WHERE INTERNO=@INTERNOS
the above SP will return a recordset with 3 fields and 2 output parameters, each of them will have a single data value from different records.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 3:06 pm
Thanks for your example. After trying it in practice I'm getting the idea.
Regards,
-Benton
April 26, 2009 at 11:51 pm
what i understand regarding your post, due to that,
Yes, when I create the procedure that way it returns a recodset of several records. So in short, the answer to my question is yes, right?
OUTPUT parameters are already variables to store some data from the procedure, which have a specific datatype; and they can hold as well as return the value according to that datatype only.
So, if you declare an OUTPUT parameter, say @out Varchar(30), in a stored procedure, and do something like this,
some operation
@out = 'return value1'
some operation......
@out = 'return value2'
some operation.......
@out = 'return value3'
this will return 'return value3' as result to your output parameter...
So, to answer to your question... YES, output parameter will return only one value at a time...
Though you can declare output parameter as per your need; like varchar, datetime, integer for single valued result, OR declared it as a 'table' for recordset type result.
Hope this will help.
"Don't limit your challenges, challenge your limits"
April 28, 2009 at 11:16 pm
Hi,
Stored procedure can return n number of output parameter that you specified while creating stored procedure.
So it is false that stored procedure return only one value.
April 28, 2009 at 11:48 pm
Stored procedure can return n number of output parameter that you specified while creating stored procedure.
So it is false that stored procedure return only one value.
Absolutely right... any stored procedure can define n # of OUTPUT parameters.
But, here the point is, can an OUTPUT parameter return a set of records OR return only single value?
"Don't limit your challenges, challenge your limits"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply