"a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit". Stored procedures allow a more flexibility offering capabilities such as conditional logic .Bandwidth and execution time are reduced because stored procedures are stored within the DBMS,. because a single stored procedure can execute a complex set of SQL statements. SQL Server pre-compiles stored procedures such that they execute optimally and client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.
To add a new stored procedure to the Northwind Microsoft SQL database, perform these steps.
- Load SQL Server Enterprise Manager, found in the Microsoft SQL Server program group.
- Expand the tree "Microsoft SQL Servers," "SQL Server Group," and locate your database server. With SQL installed locally, the default server will be (LOCAL
- Locate and expand the Student database. Select Stored Procedures. On the right side of the screen, you'll see any default stored procedures included with the student database.
- Right-click anywhere on the right pane of the Enterprise Manager and select New Stored Procedure.
Simple insert Stored Procedure
use student create procedure studentinsertproc @stdid text,@subid text,@subname text,@mark int as insert into student (stdid ,subid,subname,mark)values(@stdid,@subid,@subname,@mark) studentinsertproc '10001','101','computer graphics',85 studentinsertproc '10002','101','computer graphics',65, studentinsertproc '10003','101','computer graphics',75 studentinsertproc '10004','101','computer graphics',55 studentinsertproc '10005','101','computer graphics',85 studentinsertproc '10006','101','computer graphics',87 select * from student
- select and press F5