How to create stored procedure in SQL?

Tweet

SQL STORED PROCEDURE :

1. SQL Procedure are nothing but set of sql statement grouped and perform a specific task. it will give high performance for database
2. stored procedure compiled at once it created in database Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan
3. it is usability of SQL code and re-usability purpose of same kind of operation using twice in our source code

How to create stored procedure ?

 SYNTAX:

 CREATE PROCEDURE <PROC NAME>
 AS
 @param1 <datatype>
 BEGIN
  //Code of T-SQL
 END


EXPLAINATION : 

 CREATE PROC <PROC NAME>
 AS
 @param1 BIGINT  //input param
 BEGIN

 CREATE TABLE #TEMP (name VARCHAR(50)) // CREATiNG TEMP TABLE
 INSERT INTO #TEMP    // bulk insert
 SELECT * FROM table1 WHERE salary > @param // get users from table 1 
  UNION 
 SELECT * FROM table2 WHERE salary < @param  // get users from table 2

 SELECT * FROM #TEMP
 DROP TABLE #TEMP

 END

How to alter stored procedure ?

 SYNTAX:

 ALTER PROCEDURE <PROC NAME>
 AS
 @param1 <datatype>
 BEGIN
  //code of T-SQL
 END

EXPLAIN : 
//instead of create we can give alter and we can save changes in sp(STORED PROC)

EXPLAINATION : 

 ALTER PROC <PROC NAME>
 AS
 @param1 BIGINT   //input param
 BEGIN

 CREATE TABLE #TEMP (name VARCHAR(50)) // CREATiNG TEMP TABLE
 INSERT INTO #TEMP    // bulk insert
 SELECT * FROM table1 WHERE salary > @param // get users from table 1 
  UNION 
 SELECT * FROM table2 WHERE salary < @param  // get users from table 2

 SELECT * FROM #TEMP ORDER BY name DESC
 DROP TABLE #TEMP

 END

How to execute stored procedure ?

 SYNTAX:
 EXECUTE <sp_name> @param = 1000
  OR
 EXEC <sp_name> 1000
  OR 
 <sp_name> 1000

Summary : Stored procedure is reusing the code,performance of execution plan,reduce the traffic of the network sp(STORED PROC) is one of the best way to use in heavy transactions over the network