stored procedure

[GSMike]

I'm using v6.5.
I've never created a stored procedure before.
When I bring up the Manage Stored Procedures dialog box, it says CREATE PROCEDURE <PROCEDURE NAME> AS.
Can you tell me what do I do from here?
Do I replace <PROCEDURE NAME> with my own new name (e.g.: MyFirstSP)??
Then what do I type after AS? Do I begin my T-SQL statement there (e.g. Select * from mytable).
Then, after this is complete, how do I run the procedure??
Thanking you in advance!! Mike K

 

[bperry]

Yes, you are on the right track. For example:
---------------
CREATE PROCEDURE sprGetClients
as
Select * from Clients
------------------------
will return a record set to the client that called the SP


But this is verry basic, and much more can be done. For instance, it is very common to pass parameters into the SP.
for example:
-------------------------
CREATE PROCEDURE sprGetClients (
@parmClient int = null
)
AS
if @parmClient IS NULL
Select * from Clients
else
Select * from Clients Where ClientId = @parmClient
------------------------
It is also common to have the SP pass back a 'Return Value', and/or Output parameters to the calling program.


To execute the SP, it depends on the client that you are using. With Query Analyzer, you would do
exec dbo.sprGetClients

But in VB, Dephi, ASP, etc, the protocol and syntax to call the Sp varies.

You will want to get some SQL books that have chapters on coding SPs.

rgrds, etc
brian perry

 

[GSMike]

Fantastic!!!
Thank you, Brian Mike K