Returning Info from Stored Procedures

Question

Returning Info from Stored Procedures

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

I've edited the question, to note data and directly returning, which should eliminate temp tables, etc. I think Print/Raiserror aren't really about returning data, but they do return info, so I've reworded things.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Calling another procedure isn't returning data from the procedure.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

I got it right only because I mentally limited the question to "typical documented" methods. It's also why I hate tests. It's like asking the question of "Which is the following is the BEST way to count from 1 to a million in SQL Server?" and then none of the answers having either the Cross Join method or Itzik Ben-Gan's cCTE (Cascading CTE method) method listed as one of the choices.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

Steve Jones - SSC Editor wrote:

Calling another procedure isn't returning data from the procedure.

The procedure being called might also return data. In this code test_proc1 calls test_proc2. Both procedures contain a single select statement. At the end of running test_proc1 there are 2 tables output. test_proc1 uses test_proc2 to output data.

drop proc if exists dbo.test_proc1;
drop proc if exists dbo.test_proc2;
drop type if exists dbo.test_type1;
go

create type dbo.test_type1
as
table(
  set_num                    int unique not null,
  repetitions                int not null check(repetitions between 1 and 100),
  weight_lbs                int not null);
go

create proc dbo.test_proc1
as
set nocount on;
declare
 @example_type                dbo.test_type1;

insert @example_type(set_num, repetitions, weight_lbs)
values (1, 10, 125),(2,10,150),(3,8,175);

exec dbo.test_proc2 @example_type;

select * from @example_type;
go

create proc dbo.test_proc2
  @input_table            dbo.test_type1 readonly
as
select * from @input_table;
go

exec dbo.test_proc1;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 5

I'm going to be pedantic (surprise! Surprise!). What do you mean by "return info" from a procedure? Technically if it returns a single value the programming structure is a function (mathematical definition as well as a common programming language construct). A procedure can only return a status (success, failure, raise error code, etc). This is metadata.

However, the SQL/PSM Standards allow the parameters of a procedure to be declared as IN <parm>, OUT <parm> or IN OUT <parm>. These conventions mean that an argument (an argument is an actual value that replaces a parameter in an invocation) is used for input only, output only or both an input and output (modified). If you're really old, you might remember Algol 60, which introduced us to options for a call by name, call by value and call by reference in programming languages.

Since you're never sure what the host language in which SQL is going to be embedded might be now or in the future, it's probably a good programming technique to keep it as simple as possible.

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

I'm a bit confused about "Return values" being plural. Isn't there only a "return value"? Yes, somewhat pedantic mood as well :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

jcelko212 32090 wrote:

I'm going to be pedantic (surprise! Surprise!). What do you mean by "return info" from a procedure? Technically if it returns a single value the programming structure is a function (mathematical definition as well as a common programming language construct). A procedure can only return a status (success, failure, raise error code, etc). This is metadata.
However, the SQL/PSM Standards allow the parameters of a procedure to be declared as IN <parm>, OUT <parm> or IN OUT <parm>. These conventions mean that an argument (an argument is an actual value that replaces a parameter in an invocation) is used for input only, output only or both an input and output (modified). If you're really old, you might remember Algol 60, which introduced us to options for a call by name, call by value and call by reference in programming languages.
Since you're never sure what the host language in which SQL is going to be embedded might be now or in the future, it's probably a good programming technique to keep it as simple as possible.

Wow, I must be really old since I actually learned the basics of ALGOL. I actually liked the language. It was my first block oriented language.

I wouldn't be surprised if there are ALGOL programs still be used today considering COBOL and FORTRAN are still used today as well.

Stewart "Arturius" Campbell SSC Guru Points: 72625 More actions · Answer 8

Nice one, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”