Stored Procedure

[Varne]

Hi

What is a stored procedure on Access or SQL? Is it simply a query? When we
run a query does compilation takes place like when we run VBA procedures?

Could someone clarify?

Thanks
Varnen M

 

[Stefan Hoffmann]

hi Varnen,

What is a stored procedure on Access or SQL?

A stored procedure is a method or "batch" consisting of SQL statments.
Access has none. SQL Server and mostly all "real" RDBMS have them.

Is it simply a query?

Nope.


mfG
--> stefan <--

 

[Banana]

Hi

What is a stored procedure on Access or SQL? Is it simply a query? When we
run a query does compilation takes place like when we run VBA procedures?

Could someone clarify?

Thanks
Varnen M

As a matter of clarification; Access (or more specifically, Jet, the
database engine that Access uses) doesn't support stored procedures.
That's okay because it comes with VBA which would be then used to
replicate several of functions.

Stored procedure is a concept available in several major RDBMS product
such as SQL Server, MySQL, Oracle, DB/2, etc. You could think of it as
programming using SQL with few procedural constructs. The point of using
SP is to be able to call single function or procedure which would
actually process several queries, set various variables,
insert/update/delete rows, and return the result back to the client.
This is preferable to having the client submitting all those queries
necessary to accomplish the same process.

I'm sure that if you want to learn more, you can find more information
about SP, especially those specific to a RDBMS product via a Google search.

In Access linked to a RDBMS backend, SP can be called by either
pass-through queries or ADO Command object.

HTH.

 

[Alex Dybenko]

Hi Stefan,
actually help states that there is a CREATE PROCEDURE Statement:

Creates a stored procedure .
Note The Microsoft Jet database engine does not support the use of CREATE
PROCEDURE, or any of the DDL statements, with non-Microsoft Jet database
engine databases.

but AFAIR only simple select works there


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Varne]

Hi Stefan

Thank You.

In Access the queries are kept as SQL statements. Wrong? I do not understand
what you mean by a 'real' relational database management system. Why is not
Access a real one?

M Varnendra

 

[Stefan Hoffmann]

hi Varnen,

In Access the queries are kept as SQL statements. Wrong?

This right. On big point is that other SQL dialects as T-SQL or PL/SQL
have some conditional and loop statments, e.g. IF, FOR etc. Jet SQL has
none of these.

I do not understand
what you mean by a 'real' relational database management system. Why is not
Access a real one?

This was irony.

But besides that I always make this differentiation: Access is the
management and development IDE, the database engine is Jet/ACE. The
database is the *.mdb/*.accdb file.


mfG
--> stefan <--

 

[Varne]

Hi Ban

I think I am close. Please tell me if what I say below is wrong; Please put
up with if it looks childish;

When you write an SQL procedure in Access you cannot do a sub procedure
within the main procedure like we do in VBA. Example;

Sub MainProcedure ()
Workbooks.Open "kfgr.xls" 'Procedure 1
Cells(1,1).Select 'Procedure 1
Call hhvsajc 'Procedure 2
End Sub




And




We cannot delare variables within the SQL statements we build in Access.
Example;

SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber)=14283));

If I want 14284 I should do a seperate query whereas in a stored procedure I
can do 'Dim TransactionNumber as Integer' and change the number without doing
an other query.

Please come back.

Thanks.

Varnen M

 

[Varne]

Thanks Stefan

I think I am close to understanding it. Please see my response to Banana.
But I have used 'if' in Access SQL!

An other professional like you told me the difference between Jet and
Access(Front End) but I thought you meant something else. If you do not mind
please compare it with MS SQL and point out the difference.

Thanks.
Varnen M

 

[John W. Vinson]

Hi Ban

I think I am close. Please tell me if what I say below is wrong; Please put
up with if it looks childish;

When you write an SQL procedure in Access you cannot do a sub procedure
within the main procedure like we do in VBA. Example;

Sub MainProcedure ()
Workbooks.Open "kfgr.xls" 'Procedure 1
Cells(1,1).Select 'Procedure 1
Call hhvsajc 'Procedure 2
End Sub

That is correct; you cannot. What you have above is good (Excel dialect of)
VBA, and with the appropriate library references could be called from an
Access VBA Module, but it is not valid SQL (not in Access/JET SQL or to my
knowledge any other flavor of SQL).

And




We cannot delare variables within the SQL statements we build in Access.
Example;

SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber)=14283));

If I want 14284 I should do a seperate query whereas in a stored procedure I
can do 'Dim TransactionNumber as Integer' and change the number without doing
an other query.

This is valid SQL. You cannot Dim a variable - but you can come awfully close,
using a Parameter instead.

SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber) = [Enter Transaction Number:]));

will pop up a prompt on the screen saying "Enter Transaction Number:" and the
search will return that result.

It's optional (but usually prudent) to declare the parameter in the Query:

PARAMETERS [Enter Transaction Number:] Integer;
SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber) = [Enter Transaction Number:]));

In practice, it's very common and desirable to use a reference to a Form
object as a parameter. The query will pull the value from that form object (if
the form is open!!) rather than popping up a prompt. E.g.

=[Forms]![frmCriteria]![txtTransNo]

will use whatever value exists in the textbox named txtTransNo on frmCriteria
as a criterion.

 

[Varne]

Hi John

Thanks John. So what exactly is a 'Stored Procedure'? Could you please
demonstrate a simple 'Stored Procedure'. In my computer I see a directory
called MS SQL Server 2005. Is it something like Access/Jet or is it something
providing connectivity to a Server sitting in MicroSoft premises. Provided
you give me the example can I use it on the SQL 2005?

Thanks.
Varnen M

Hi Ban

I think I am close. Please tell me if what I say below is wrong; Please put
up with if it looks childish;

When you write an SQL procedure in Access you cannot do a sub procedure
within the main procedure like we do in VBA. Example;

Sub MainProcedure ()
Workbooks.Open "kfgr.xls" 'Procedure 1
Cells(1,1).Select 'Procedure 1
Call hhvsajc 'Procedure 2
End Sub

That is correct; you cannot. What you have above is good (Excel dialect of)
VBA, and with the appropriate library references could be called from an
Access VBA Module, but it is not valid SQL (not in Access/JET SQL or to my
knowledge any other flavor of SQL).

And




We cannot delare variables within the SQL statements we build in Access.
Example;

SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber)=14283));

If I want 14284 I should do a seperate query whereas in a stored procedure I
can do 'Dim TransactionNumber as Integer' and change the number without doing
an other query.

This is valid SQL. You cannot Dim a variable - but you can come awfully close,
using a Parameter instead.

SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber) = [Enter Transaction Number:]));

will pop up a prompt on the screen saying "Enter Transaction Number:" and the
search will return that result.

It's optional (but usually prudent) to declare the parameter in the Query:

PARAMETERS [Enter Transaction Number:] Integer;
SELECT BCST.TransactionNumber
FROM BCST
WHERE (((BCST.TransactionNumber) = [Enter Transaction Number:]));

In practice, it's very common and desirable to use a reference to a Form
object as a parameter. The query will pull the value from that form object (if
the form is open!!) rather than popping up a prompt. E.g.

=[Forms]![frmCriteria]![txtTransNo]

will use whatever value exists in the textbox named txtTransNo on frmCriteria
as a criterion.

 

[vanderghast]

A stored procedure of one single statement is like a parameter query. In
fact, with Jet 4.0, you can define a PROC which will be equivalent to a
parameter query:


CurrentProject.Connection.Execute "CREATE PROC testing(who LONG, alias
TEXT) AS INSERT INTO myTable(id, cieName) VALUES(who, alias)"


and you can execute it (arguments are simply supplied, no ( ) around
hem ) :

CurrentProject.Connection.Execute "EXEC testing 22, ""twenty-two"" "


Since they basically behave exactly like parameter queries, they can be
considered 'compiled' in the same cases.

Note that you probably have to use an ADO connection,
CurrentProject.Connection.Execute , not a DAO database, not
CurrentDb.Execute, on the string representation of the SQL statement, if you
explicitly use PROC[edure] and EXEC[ute], as shown here up.


Vanderghast, Access MVP

 

[vanderghast]

hi Varnen,

This right. On big point is that other SQL dialects as T-SQL or PL/SQL
have some conditional and loop statments, e.g. IF, FOR etc. Jet SQL has
none of these.

(...)

Access+Jet uses VBA for its 'if then else', or for its user defined function
and logic. And VBA is much more general than T-SQL.



Vanderghast, Access MVP

 

[John W. Vinson]

Hi John

Thanks John. So what exactly is a 'Stored Procedure'? Could you please
demonstrate a simple 'Stored Procedure'. In my computer I see a directory
called MS SQL Server 2005. Is it something like Access/Jet or is it something
providing connectivity to a Server sitting in MicroSoft premises. Provided
you give me the example can I use it on the SQL 2005?

Open the MS SQL Server 2005 folder in "All Programs"; select "Documentation
and Tutorials"; click on "Books Online"; and search for "Stored Procedures".

I'm sorry, but this is a deep and complicated subject and as an unpaid
volunteer, I'm not going to try to write an encyclopedia entry on it when it's
already well documented on your own computer!

A Stored Procedure works in SQL, it does not work in Access (with the
exceptions noted elsethread). It's a way of writing procedural language in
conjunction with a SQL query. Saying it's "something like Access/JET" is a bit
like saying "the Indianapolis 500 is something like a Ford F250 pickup truck";
they're not even comprable terms.

 

[David W. Fenton]

As a matter of clarification; Access (or more specifically, Jet,
the database engine that Access uses) doesn't support stored
procedures. That's okay because it comes with VBA which would be
then used to replicate several of functions.

There's a terminology problem here. Jet 4 with ANSI 92 SQL added the
ability to use CREATE PROCEDURE DML to create saved QueryDefs. They
don't have any procedural code, so aren't by any stretch of the
imagination "stored procedures," but many db people coming to Access
who lack anything corresponding to the saved QueryDef see this as
meaningful, since it fits in with their model of what's going on.
For them it's the STORED part of Stored Procedure that they see as
meaningful here, while we take that for granted with our views
(saved SELECT statements) and DML queries (saved "Action" queries)
and see the important part of "Stored Procedure" as being the
PROCEDURE part, which Jet/ACE SQL lacks in all flavors (and always
will, since there's no server-side process to queue and prioritize
statements sent from multiple users).

 

[AccessVandal via AccessMonster.com]

You might want to look at ADPs instead of MDBs. At the same time, look into
Help on ADP.

 

[Banana]

You might want to look at ADPs instead of MDBs. At the same time, look into
Help on ADP.

Why? Last time I checked, there has been no new functionalities added to
ADPs, and the word from MS is that using .mdb/.accdb is recommended over
..adp.

Furthermore, using .adp rob you of the benefit of having a local
database engine and IMHO hurts the scalability/concurrency because you
have to collect all lookup tables and do all data processing server side
even when it could have been done client side (especially in case of
reporting upon snapshots, for example), not to mention that you can't
use anything else but SQL Server.

Finally, you can still use results of stored procedure and bind it to
the form; just use ADO in a .mdb and it can be bound to a form with full
updatability as desired.

 

[AccessVandal via AccessMonster.com]

It's not about why. It's about learning to use Stored Procedure in ADP as the
OP doesn't have any idea about them. At least the OP can see what is Stored
Procedure in the SQL Server.

In all due respect, there's little advantages using ADPs. I can understand
your reasons to opposed ADPs.

Say, was this quote you put in another forum?

"My favorite superhero: ISO9000 Lad! He wins by merely describing how he
might theoretically beat you, but he NEVER ACTUALLY DOES ANYTHING! Villains
get utterly overwhelmed by the sheer multitude of paper documents he
dispenses from his Compliance And Certification Apparatus (CACA), and just
die under the weight of the bureaucratic load."

 

[Banana]

It's not about why. It's about learning to use Stored Procedure in ADP as the
OP doesn't have any idea about them. At least the OP can see what is Stored
Procedure in the SQL Server.

Ah, gotcha. I can see where you're coming from. I'm inclined to say that
SSMS may provide to be a better teaching, especially if the OP has
acccess to SSMS 2008 which has significantly improved the intellisense,
but even so, doing it in SSMS 2005 seems to me make more sense than
using ADP. But that's just me. If it happen that ADP does a better of
showing how SP works, more power to OP.

Say, was this quote you put in another forum?

LOL. Good one. It was a signature I had in the other forum for a while,
but that isn't actually my words; someone much more clever than I am
came up with it and I "borrowed" it as my signature. That's long ago and
I"m surprised it's still floating around.

 

[AccessVandal via AccessMonster.com]

Ah, gotcha. I can see where you're coming from. I'm inclined to say that
SSMS may provide to be a better teaching, especially if the OP has
acccess to SSMS 2008 which has significantly improved the intellisense,
but even so, doing it in SSMS 2005 seems to me make more sense than
using ADP. But that's just me. If it happen that ADP does a better of
showing how SP works, more power to OP.

That's true, if you're familiar SQL Server. It's just my opinion its much
easier for the OP. I doubt the OP knows anything about SQL Server.

LOL. Good one. It was a signature I had in the other forum for a while,
but that isn't actually my words; someone much more clever than I am
came up with it and I "borrowed" it as my signature. That's long ago and
I"m surprised it's still floating around.

Yep, that was a good one. Stumble that when I was doing a google search.