A stored procedure, a collection of named T-SQL statements stored on a server, is a way to encapsulate repetitive work.
Why use stored procedures?
All clients can ensure consistency of data access and modification through the same stored procedures.
Permissions control, you can individually assign execute permissions for the stored procedure.
The stored procedure is packaged for easy modular design
Stored procedures can reduce network traffic
Create a stored procedure
CREATE Proc[edure] Procedure_name[;number]
[{@parameter data_type}[varting][=default][output][,... n]
[With
{RECOMPILE | Encryption | RECOMPILE |,encryption}]
[For Repilcation]
As SQL_STATEMENT[...N]
eg. create a simple memory
CREATE proc Customers
As
SELECT * FROM Customer information
Go
Execution Memory: Exec customers
Memory containing input parameters
create proc bracelet cost @begincount float, @endcount float
As
If @begincount is null or @endcount is NULL
Begin
RAISERROR (' Null is Disable ', 14,1 ')
Return
End
Select ... where A. Percent of cost between @begincount and @endcount
exec bracelet Cost 65,75
SQL SERVER stored procedures and triggers