Full Stored Procedures?

[sonia.sardana]

Hi frnds, can somebody has the full material of stored procedures, cz i have no knowledge regarding it.

1) How to Create Stored procedures?
2) Why they are used?
3) When they are created?
4)What is the need of stored Procedures?

Your Reply will be highlt appreciated.

[sleepydog3]

At the core a stored procedure (proc) is a saved select/insert/update/delete statement.

You use a PROC automate repetitive taskes and reduce changes for errors additionally you can get performance enchance from a well formed PROC.

PROC's also reduce the threat of SQL injection attacks on your DB.

You can create a PROC at any time. before durning and after development.


http://msdn2.microsoft.com/en-us/library/ms187926.aspx

[Delerna]

Hi sonia
You've asked a few questions about stored procedures, you must be having fun coming to terms with them.

1) Open enterprise manager and using the tree view on the left.
a) Open the database you want to work with.
b) Right click on 'stored procedures' (assuming you are right handed)
c) Select 'New Stored Procedure' from the context menu.
A window with something like this will open.

   CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
 

you will need to change it to something like this

   CREATE PROCEDURE prYourProceduresName 
       --here you can put a list of parameters that must be provided 
       --when you call the procedure from somewhere. for example
       @FirstParam int, @SecondParam varchar(50), @ThirdParam datetime
   AS
      -- This is where you put the code that performs the desired functionality
      --  For example
    SELECT @FirstParam, @SecondParam,@ThirdParam 
   GO
 

now if you execute the following code in query analyser

   exec prYourProceduresName 2,'testing','2007-01-01'
 

that should give you some idea of how to create and execute a stored procedure.
You can execute them from
other stored procedures
asp pages
access databases
windows scripting host
etc etc

[Delerna]

2) Why are they used.
Well they are used for many reasons but i think behind many of them is that stored procedures provide a mechanism that allows you to group a set of related actions together and have them all execute with a single call.

for example, suppose you had a task that required you to
deleted a set of records from 1 table
log who and when they were deleted into another table
save the deleted records into a third table.
return the contents of the first table after the deletion to the user.

suppose also that you were doing that from a web page via vb or java script.
Well you could carry out each of the above one at a time from the web page.
But there is the overhead of setting up data ojects to perform each function, network traffic caused between user and the SQL database etc

Wouldn't it be better to have all those things in a single stored procedure so that a single call to it from the web page caused all of that to happen on the server itself. All the web page has to do is call the proc and wait for and then display the records that were returned.

I hope that explanation helps you.
Just realise one thing.
How you actually use stored procedures is up to your own imagination

[Delerna]

3) When are they created
Well relating the question to terms of programming in c vb or java they are created at design time. They could be created and deleted on the fly, ie run time.But I can't think of any reason why you would want to.

4) What is the need of stored procedures
They were invented so as to provide you with a way of performing a set of tasks as a unit and to do it on the server so as to reduce the communication overheads between server and client. At least, thats my understanding.

Sonia, experiment with them and you will soon get the idea.
Look for sets of actions that are always carried out together in a sequence.
Also Update and delete queries.
All of these things are candidates for stored procedures.

[sonia.sardana]

Thx for replying both delerna and Sleepydog3.....
Hey on sql 2000, i m able to create Stored Procedures..... ..but on SQL 2005 when i rght click on database I want to work with..there is no stored Procedure....

Second thing I inserts the record into stored Procedure,Now i want to select the records from stored procedure.....S uppose my procedure name is ProSonia

I write ,,, Select * from ProSonia
It gives error.......

[Delerna]

try
exec ProSonia

instead of

select * from ProSonia

Although you havent posted the code for ProSonia so........

Anyway stored procedures are not views or tables and
select * from whatever
is for use with views and tables, not strored procedures


I've never used 2005 so I can't comment on that except to say
surely its not that different, is it?

[sonia.sardana]

ya there is not so much of difference between 2000 and 2005.

One diff is that in 2000 we have Query analyzer and Enterprise manager.
Query Analyzer--Where we write and execute Statements.
Enterprise manger--Where we see our tables and databases.

2005--Both are combined. To write statements,We click the New button on the top of the screen.....
Screen is divided into two parts-
L.H.S--Databases
R.H.S.---Write Statements.

Simple......... ...But in 2005 option to create Stored procedures is not same