stored procedures...

  • Thread starter Thread starter francois
  • Start date Start date
F

francois

Hi,

I am pretty new about stored procedures things. I always believe in the past
they were evil for the sake of DB independance. A few years back I worked in
a place where we all use ANSI sql to retrieve anything we need. Also
sometimes I needed to instantiate heaps of objects to finally reject half of
them that i did not need (which could cost quite some time in large tables)

Getting older (and wiser? :p) I am not so sure about it anymore as a stored
procedure let you make some computing to retrieve only the data you want to
select. then you wont have to eliminate (skip) records manually in the
resultset (thing that you cannot do anyway if you use databiding.

Then i got 2 questions :

1. Was I stupid to believe that to keep DB independance I should not use
stored procedure? I think it may be quite easy to translate a stored
procedure from a DB to an other. I assume all professional DB provides the
same kind of functionality. I just assume this then I would like some
confirmation or infirmation

2. Also I am very newbie about stored procedure. Do anyone knows a good
resource to learn about it? a book, a website? I am interested in SQL Server
at a first. I found a few tutorials but they are either to simple ot then
too complex. nothing that would bring me from the level of newbie to
advanced as I think the real power of stored procedure only appears when you
start to know more about it and what kind of computing you can do in it.

Best regards,

Francois
 
Francios:

Nothing stupid about wanting DB independence, but if you need to look at it
in terms of what you get compared to what it costs.

Stored Procs are faster, much more secure and make maintenance much easier
(you can change the behavoir of your program without having to recompile it
and redistribute it for instance).

Most every real DB supports them and you even access has an implementation
of them. They are the ideal way to implement backend logic and they are
here to stay. Also, your standard SQL Statements can easily be turned into
procs (in many instances just adding CREATE PROCEDURE usp_ProcedureName As
YourSQL Here
is all it takes.

Check out www.sqlservercentral.com or just type Stored Procedure SQL Server
2000 or Oracle in Google, there are tons of resources out there..
 
francois said:
Hi,

I am pretty new about stored procedures things. I always believe in the past
they were evil for the sake of DB independance. A few years back I worked in
a place where we all use ANSI sql to retrieve anything we need. Also
sometimes I needed to instantiate heaps of objects to finally reject half of
them that i did not need (which could cost quite some time in large tables)

Getting older (and wiser? :p) I am not so sure about it anymore as a stored
procedure let you make some computing to retrieve only the data you want to
select. then you wont have to eliminate (skip) records manually in the
resultset (thing that you cannot do anyway if you use databiding.

Then i got 2 questions :

1. Was I stupid to believe that to keep DB independance I should not use
stored procedure? I think it may be quite easy to translate a stored
procedure from a DB to an other. I assume all professional DB provides the
same kind of functionality. I just assume this then I would like some
confirmation or infirmation
Click to expand...

If you want the ultimate in data independence, you have to go down to the
Least Common Denominator, which is probably embedded SQL. But, there are
some databases that are not fully ANSI compatible, so you will likely never
achieve 100% independence.

Realistically, you can maintain independence via stored procedures, as you
will be working primarily with relational database servers in Enterprise
development. Moving from SQL Server to Oracle is largely a matter of calling
a stored procedure a bit differently.
2. Also I am very newbie about stored procedure. Do anyone knows a good
resource to learn about it? a book, a website? I am interested in SQL Server
at a first. I found a few tutorials but they are either to simple ot then
too complex. nothing that would bring me from the level of newbie to
advanced as I think the real power of stored procedure only appears when you
start to know more about it and what kind of computing you can do in it.
Click to expand...

There are quite a few great books on T-SQL for SQL Server 2000 on the
market. I would look at Amazon. If you find a few and do not want to spend
as much, Safari Online is a great resource (www.safaribooksonline.com). You
have to like to read online, however.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Back
Top