By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

Full Stored Procedures?

P: 95
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.
Feb 27 '08 #1
Share this Question
Share on Google+
7 Replies


sleepydog3
P: 7
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
Feb 27 '08 #2

Delerna
Expert 100+
P: 1,134
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.
Expand|Select|Wrap|Line Numbers
  1.    CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
  2.  
you will need to change it to something like this
Expand|Select|Wrap|Line Numbers
  1.    CREATE PROCEDURE prYourProceduresName 
  2.        --here you can put a list of parameters that must be provided 
  3.        --when you call the procedure from somewhere. for example
  4.        @FirstParam int, @SecondParam varchar(50), @ThirdParam datetime
  5.    AS
  6.       -- This is where you put the code that performs the desired functionality
  7.       --  For example
  8.     SELECT @FirstParam, @SecondParam,@ThirdParam 
  9.    GO
  10.  
now if you execute the following code in query analyser
Expand|Select|Wrap|Line Numbers
  1.    exec prYourProceduresName 2,'testing','2007-01-01'
  2.  
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
Feb 27 '08 #3

Delerna
Expert 100+
P: 1,134
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
Feb 27 '08 #4

Delerna
Expert 100+
P: 1,134
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.
Feb 27 '08 #5

P: 95
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.....Suppose my procedure name is ProSonia

I write ,,, Select * from ProSonia
It gives error.......
Feb 28 '08 #6

Delerna
Expert 100+
P: 1,134
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?
Feb 28 '08 #7

P: 95
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
Feb 28 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.