how can i write a stored procedure

  • Thread starter Thread starter strong
  • Start date Start date
S

strong

hi all
how can i write a procedure?

for example:
i write followed:
procedure "sp1"
parameters @arg1 long
select * from tbl1
select * from tbl2

but,it exists syntax error,how to write it?
thanks for your reply
 
Hi,


With Jet, a stored procedure can only hold one statement. It is like
a parameter query, but if you want to define it like a sproc:


CREATE PROCEDURE procName( parameter datatype , parameter
datatype, ... ... , ... ... , ... ... ) AS sql_Statement

You will have to use ADO, since DAO and the query designer (based on
DAO) won't, probably, like this Jet 4.0 extension.



To run a sequence of queries, use VBA.



Hoping it may help,
Vanderghast, Access MVP
 
You will have to use ADO, since DAO and the query designer (based
on
DAO) won't, probably, like this Jet 4.0 extension.
Click to expand...


We now have new NAMES: action query = procedure; select query = view: but
those new names do not imply any new functionality....

----------
DAO and JET:

PROCEDURE name [param1 datatype[, param2 datatype[, ...]]
An SQL procedure consists of a PROCEDURE clause (which specifies the name of
the procedure), an optional list of parameter definitions, and a single SQL
statement.

example:
Procedure MyQDF
INSERT INTO tblDiary_Report ( Status )
SELECT DISTINCTROW "Cash" AS Status FROM qryDiary_FXP_Base;

However, the name used for the stored procedure is the name you supply to
Access when you save the procedure, or to the CreateQueryDef method when you
create the procedure. Furthermore, the keyword 'Procedure' and the
procedure name given in the SQL, are stripped out of the sql when the
procedure is saved: if you read back a View, it looks like this:
select * from MyTable;
-----------

JET does not directly support complex transactional syntax in stored
procedure, like that of Transact-SQL. Instead, JET, when called from
Access, can call User Defined Functions written in VB, evaluated by the VBA
virtual machine. Since this is only available when the stored procedure is
executed from Access, VB and C# programmers are limited to using only simple
SQL if they use a JET database. Still, they can, and always have been able
to, store the SQL in the database, and execute it directly, by issuing an
execute instruction to the db engine. (Access programmers rarely link
directly to a stored view/action query in remote JET database, but that is
because Access programmers have a local JET database, not because the
functionality was missing)

Since JET SQL was specified, there has been a new ANSI SQL standard, which
as expected includes many of the standard SQL extensions, including many of
the JET extensions. JET (when called from ADO) now supports two SQL syntaxes
'JET SQL' and 'ANSI SQL' The 'ANSI SQL' syntax is also supported by SQL
Server.

One of the features of the 'ANSI SQL' is that includes DDL (Data Definition
Language) for storing SQL. That is, it includes CREATE VIEW and CREATE
PROCEDURE. Previously, the only way to store an SQL procedure (action
query) or view (select query) in a JET database was to use DAO methods or
the Access Interface. However, the Procedures/View/action query/select
query/ that can be stored still may not use Transact-SQL.

What you think about this may depend on your point of view: If you didn't
realise that a saved Action Query was a stored procedure, you may be
impressed: if you did realise, you may be amused; if you were looking for
Transact-SQL and Server Extensions, you may be disgusted...

(david)
 
Hi,


There is some new "functionnalities" for the developper. As example, a
parameter query presented as a stored procedure is easier to call, since the
parameters are now presented as arguments, rather than having to use a
COMMAND object or to walk through a PARAMETERS collection.


VBA has always be to Jet what the t-sql is to MS SQL Server... just that
VBA is, indeed, an all purpose programming language, thing that t-sql would
never ever have the pretension to be. So, with Jet, if you need to glue your
statements, you use VBA (inside a transaction, if required). If you need VBA
subroutine stored in Access to be run from VB6, as example, you can open an
Access object through automation and use its Run method.



Vanderghast, Access MVP


david epsom dot com dot au said:
You will have to use ADO, since DAO and the query designer
Click to expand...
(based
on
DAO) won't, probably, like this Jet 4.0 extension.
Click to expand...


We now have new NAMES: action query = procedure; select query = view: but
those new names do not imply any new functionality....

----------
DAO and JET:

PROCEDURE name [param1 datatype[, param2 datatype[, ...]]
An SQL procedure consists of a PROCEDURE clause (which specifies the name of
the procedure), an optional list of parameter definitions, and a single SQL
statement.

example:
Procedure MyQDF
INSERT INTO tblDiary_Report ( Status )
SELECT DISTINCTROW "Cash" AS Status FROM qryDiary_FXP_Base;

However, the name used for the stored procedure is the name you supply to
Access when you save the procedure, or to the CreateQueryDef method when you
create the procedure. Furthermore, the keyword 'Procedure' and the
procedure name given in the SQL, are stripped out of the sql when the
procedure is saved: if you read back a View, it looks like this:
select * from MyTable;
-----------

JET does not directly support complex transactional syntax in stored
procedure, like that of Transact-SQL. Instead, JET, when called from
Access, can call User Defined Functions written in VB, evaluated by the VBA
virtual machine. Since this is only available when the stored procedure is
executed from Access, VB and C# programmers are limited to using only simple
SQL if they use a JET database. Still, they can, and always have been able
to, store the SQL in the database, and execute it directly, by issuing an
execute instruction to the db engine. (Access programmers rarely link
directly to a stored view/action query in remote JET database, but that is
because Access programmers have a local JET database, not because the
functionality was missing)

Since JET SQL was specified, there has been a new ANSI SQL standard, which
as expected includes many of the standard SQL extensions, including many of
the JET extensions. JET (when called from ADO) now supports two SQL syntaxes
'JET SQL' and 'ANSI SQL' The 'ANSI SQL' syntax is also supported by SQL
Server.

One of the features of the 'ANSI SQL' is that includes DDL (Data Definition
Language) for storing SQL. That is, it includes CREATE VIEW and CREATE
PROCEDURE. Previously, the only way to store an SQL procedure (action
query) or view (select query) in a JET database was to use DAO methods or
the Access Interface. However, the Procedures/View/action query/select
query/ that can be stored still may not use Transact-SQL.

What you think about this may depend on your point of view: If you didn't
realise that a saved Action Query was a stored procedure, you may be
impressed: if you did realise, you may be amused; if you were looking for
Transact-SQL and Server Extensions, you may be disgusted...

(david)



Michel Walsh said:
Hi,


With Jet, a stored procedure can only hold one statement. It is like
a parameter query, but if you want to define it like a sproc:


CREATE PROCEDURE procName( parameter datatype , parameter
datatype, ... ... , ... ... , ... ... ) AS sql_Statement

You will have to use ADO, since DAO and the query designer
Click to expand...
(based
on
DAO) won't, probably, like this Jet 4.0 extension.



To run a sequence of queries, use VBA.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top