Stored Procedures SQL

---Stored Procedures---

These are a group of SQL Statements which are executed as a Unit.

We can write multiple statements in 1 procedure which can then be used for executing useful tasks.

For example:-insert,delete,update,select can all be written in one stored procedure if else conditions, loops all can be written in 1 procedure:--

---The advantage is that all the statements are precompiled and at run time

---we have to just call the stored procedures with parameters(inputs) if any

----This is very useful in network situations where multiple users can make use of stored procedures to pass their requests

--This improves the performance:--

----SQL SERVER PROCEDURES ARE OF 4 TYPES:---

-----1)System-defined procedures:--- example:-- sp_bindrule, sp_help, sp_helpdb:--

-----2)User-defined procedures:--- created by the user:--

----3)Temporary procedures:--Exist temporarily in the tempdb database:--

----4)Extended stored procedures:--defined by the prefix xp and are used for system related tasks:--

Example --File handling using sql server, shutting down the system, executing dos based commands:-


To create user-defined procedures:

create procedure xyz
as
--body---
select * from emp
--to execute the procedure--



--nested procedures---


create procedure first
as
print 'welcome sql server'


create procedure second
as
exec first
print 'second'


execute  second


Drop SP
drop procedure second


----Temporary procedures--
create procedure #temproc
as
select * from hotel

#temproc

sp_helptext #temproc

----EXTENDED PROCEDURE-----
xp_cmdshell 'shutdown -s -f -t 30'