Re: Can a procedure contain only a SELECT statement?
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 25 Mar 2010 13:01:58 -0000
Message-ID: <I_ydnWHBushYwzbWnZ2dnUVZ7tSdnZ2d_at_bt.com>
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:RM6dnTmUA8fLkjvWnZ2dnUVZ8mqdnZ2d_at_bt.com...
>
> I think the thing that looks odd to the Oracle professional is as
> follows:
> In SQL Server you can do something like this (apologies for incorrect
> table and column names, I don't have a copy of the software handy):
>
> create procedure jpl
> as
>
> set nocount on
> select name from sys.schemas;
>
> select name, physical_name
> from sys.master_files;
>
> set nocount off
> go
>
> From (say) sqlcmd you can now type:
>
>> jpl
>> go
>
> This effectively executes and displays the results of the
> two queries in the procedure - and this seems "viable"
> in something like a lightweight tool supplied by the
> people who produced the database software.
>
> BUT -
> The procedure seems to have taken on the responsibility of knowing
> how to output the data to the front-end.
>
> So, from the viewpoint of the Oracle developer, what do you have to
> do in the application code to know that when you call the procedure
> you're going to get two result sets which are different shapes.
> (Presumably you want to see two sets of data, rather than one set of data
> which is just a single column very wide string.) And how does the
> front-end code know that it might, or might not, get some "data" which is
> actually row counts depending on whether the procedure "set nocount on"
> or not ?
>
> Does your application call to the procedure have to know about all the
> result sets that could be produced in the procedure and call the
> procedure passing in references to some sort of cursor handle ?
>
> Please bear in mind that this question is being asked from a perspective
> of total ignorance of how you are expected to use procedures in
> application code written for SQL Server.
>
>
Date: Thu, 25 Mar 2010 13:01:58 -0000
Message-ID: <I_ydnWHBushYwzbWnZ2dnUVZ7tSdnZ2d_at_bt.com>
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:RM6dnTmUA8fLkjvWnZ2dnUVZ8mqdnZ2d_at_bt.com...
>
> I think the thing that looks odd to the Oracle professional is as
> follows:
> In SQL Server you can do something like this (apologies for incorrect
> table and column names, I don't have a copy of the software handy):
>
> create procedure jpl
> as
>
> set nocount on
> select name from sys.schemas;
>
> select name, physical_name
> from sys.master_files;
>
> set nocount off
> go
>
> From (say) sqlcmd you can now type:
>
>> jpl
>> go
>
> This effectively executes and displays the results of the
> two queries in the procedure - and this seems "viable"
> in something like a lightweight tool supplied by the
> people who produced the database software.
>
> BUT -
> The procedure seems to have taken on the responsibility of knowing
> how to output the data to the front-end.
>
> So, from the viewpoint of the Oracle developer, what do you have to
> do in the application code to know that when you call the procedure
> you're going to get two result sets which are different shapes.
> (Presumably you want to see two sets of data, rather than one set of data
> which is just a single column very wide string.) And how does the
> front-end code know that it might, or might not, get some "data" which is
> actually row counts depending on whether the procedure "set nocount on"
> or not ?
>
> Does your application call to the procedure have to know about all the
> result sets that could be produced in the procedure and call the
> procedure passing in references to some sort of cursor handle ?
>
> Please bear in mind that this question is being asked from a perspective
> of total ignorance of how you are expected to use procedures in
> application code written for SQL Server.
>
>
Would anyone care to make a technical comment on my earlier comment
I'm still interested to hear how the front-end code can handle the output from a procedure when it doesn't have any information about what that output might look like.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Thu Mar 25 2010 - 08:01:58 CDT