Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forumdisplay.php?f=78)
-   -   Basics of Stored Procedures (http://www.go4expert.com/showthread.php?t=16842)

Safari 8Apr2009 12:34
Basics of Stored Procedures
 
SQL Server implements Client/Server technology.A number of client send queries to the Central Server.The server after receiving the query request,parses it for syntax errors and process the request.Since the query passes from the client to the server through the network it adds to network traffic.Hence an increase in the number of queries from the clients leads to network congestion and load on the server.A stored procedure can solve this problem.

It is a collection or batch of T-SQL statements and control-of-flow language that is stored under one name and executed as a single unit.It helps in improving the performance of the query.A stored procedure is a precompiled object and is readily available for the various applications to execute.

Features of stored procedures are:-
  1. Improved Performance
  2. Reduction in network congestion
  3. Enhanced accuracy
  4. Better consistency
  5. Better security mechanism

Types of procedures
  1. User-Defined stored procedures :- They are created and stored in the current database.
  2. System stored procedures :- They have names prefixed with sp_. These primarily support various administrative tasks that help manage the SQL SERVER.System stored procedures are stored in the system database and are accessible to the users of all the database.
  3. Temporary stored procedures:- It has names prefixed with the #symbol .They are stored in tempdb database and are automatically dropped when the connection terminates.
  4. Remote stored procedures :- They are the procedures that are created and stored in databases on remote servers. These can be accessed from various servers,provided user has appropriate permissions.
  5. Extended stored procedures:- These are dlls that are executed outside the SQL Server environment.
Syntax to create procedure is
Code:
CREATE PROCEDURE proc_name
AS
BEGIN
sql_statement1
sql_statement2
END
Where proc_name specifies the name of the stored procedure.

Syntax to execute procedure is

Code:
EXECUTE proc_name
Syntax to drop procedure is

Code:
DROP PROCEDURE proc_name

shabbir 3May2009 11:57
Re: Basics of Stored Procedures
 
Nominate this article of the month for April 2009

alourd 4Jun2009 18:32
Re: Basics of Stored Procedures
 
Thanks a lot for your useful article

technica 24Dec2009 14:07
Re: Basics of Stored Procedures
 
good article. But you haven't included about passing arguments to stored procedures. Inclusion of this will complete the article. Thanks for sharing.

lingoway 19Aug2010 16:47
Re: Basics of Stored Procedures
 
thanks for sharing

rameshb 14Dec2010 17:47
Re: Basics of Stored Procedures
 
You just provided an example for stored procedure would you please provide me with a real life example for procedures . I badly need it

kumarmannu 8Feb2011 13:17
Re: Basics of Stored Procedures
 
thanks tell how we will create it and and connect with the server please.


All times are GMT +5.5. The time now is 04:07.