Creating And Using Stored Procedures ??

[omarswan]

Hi,
Lately I've been hearing all these talks about Stored Procedures for database. I'm using VB6, can someone explain to me how to create and use Stored Procedures.

:) Thanks :)

[Ianpbaker]

Stored procedures are From the database, and are used to store a SQL statement that can be used over and over again without having to keep on writing it in your code. The statement can be any SQL statement (select, insert, update etc).

If You are using Microsoft SQL server then they are quite easy to create and you can find all the information you need on books online.

With Access they are not actually called Stored procedures but when you save a query you are still doing pretty much the same thing.

Both with access and SQL these can be used within VB by calling them instead of tables.

If you have any particular questions you want to ask reply back.

Good Luck

[omarswan]

Hi,
Is there a way to create and use Stored procedures in VB?
If so, How?

:) Thanks :)

[Clunietp]

You probably want to create your stored procedures using the GUI for your DBMS.... what DBMS are you using?

[omarswan]

Hi,
Is Access 97 a DMS. I just want to know how to write a program that uses stored procedures. How can I do that???

[JHausmann]

Access 97 doesn't have/use stored procedures.

[omarswan]

Hi,
All I want to know is How Can I create and Use Stored Procedures In VB that all.

:) Thanks :)

[bsmith]

You do not want to create a Stored Procedure in VB. The Stored Procedure resides in you database. If you are accessing the database via a network then you want to use stored procedures as much as possible. The stored procedure is compiled and executed on the server. You can execute a stored procedure from VB though using DAO, RDO, or ADO.

[JHausmann]

It's going to depend on the DBMS you use. You're asking the equivalent of "How do I code a text parser, in any language?".

For SQL Server 6.5, in another thread I wrote this stored procedure to replace an IIF command the person was using in Access:


if exists (select * from sysobjects where id = object_id('dbo.cp_getWeekDay') and sysstat & 0xf = 4)
drop procedure dbo.cp_getWeekDay
GO
CREATE PROCEDURE cp_getWeekDay
as


create table #tempWkDay (
DT_Import smallint not null,
DayImport char(6))

insert into #tempWkDay SELECT DT_Import, "Monday" FROM tMaster where dt_import=2
insert into #tempWkDay SELECT DT_Import, "Other" FROM tMaster where dt_import not = 2


SELECT * from #tempWkDay
GO


To create it, in VB (if it's even possible), you would send the entire code to SQL server via passthru.

To execute it, you would need to send a passthru SQL command:

cp_getWeekDay

[Edited by JHausmann on 04-05-2000 at 03:46 PM]

[omarswan]

Hi,
One of the things that I think got me confused was the idea Stored Procedure. Probably I'm taking it too literal but to me it seems as if Stored Procedures are stored inside the vb executable file, you could then use DAO, RDO, or ADO to access them.

[JHausmann]

Stored pocedures are stored in the DBMS. You can use ADO/DAO/RDo to access them. As far as VB is concerned, it's the same as any other SQL command, almost.