SQL Stored Procedure

SQL Stored Procedure?

Stored Procedures are very favourites of DBAs for reasons such as

1. They can accept input parameters
2. They can have output parameters as well & in case you don’t want to return anything. That is fine with SPs as well
3. They provide better security to data. You can allow users to execute the queries without letting them see the actual SQL Statements. Which can be of literally , hundreds of lines
4. They are super-fast. Reason is everything that is to be done with SQL Statement , such as its parsing , preparing query plan are done when a SP is created.
5. They come very handy with Dynamic SQL & to perform DDL queries.
6. Create a 100 line query & then create SQL SP on it. Now you can call those hundred lines of code with just one line & use it as many time as you desire.

SQL Stored Procedure Syntax

Note this Syntax to create a Stored Procedure

[sql]

CREATE PROCEDURE procedurename

AS

SQL Statement

[/sql]

//EXAMPLE1

[sql]

CREATE PROCEDURE HelloWorldprocedure

AS

PRINT 'Hello World'

[/sql]

For execution : EXEC HelloWorldprocedure

//OUTPUT : Hello World

//EXAMPLE2

[sql]

CREATE PROCEDURE [dbo].[UpdatePerson]

@PersonID int,

@LastName nvarchar(50),

@FirstName nvarchar(50),

@HireDate datetime,

@EnrollmentDate datetime

AS

UPDATE Person SET LastName=@LastName,

FirstName=@FirstName,

HireDate=@HireDate,

EnrollmentDate=@EnrollmentDate

WHERE PersonID=@PersonID;

[/sql]

For execution:

EXEC [dbo].[UpdatePerson]
@PersonID = 1,
@LastName = N’Ali’,
@FirstName = N’Awais’,
@HireDate = N’2002-08-06 00:00:00.000′,
@EnrollmentDate = N’2002-09-01 00:00:00.000′

It will update/add entry in UpdatePerson table.

Creating a SQL Stored Procedure with Parameters

Create SQL Server Stored Procedure with One Parameter
Example:
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
In this example we will query the Person.Address table from the AdventureWorks database, but instead of getting back all records we will limit it to just a particular city.
To call this stored procedure we would execute it as follows:
EXEC dbo.uspGetAddress @City = 'New York'
We can change the "=" to a LIKE and use the "%" wildcard in above example:
WHERE City LIKE @City + '%'

Generate Unique Random Numbers In Sql With Stored Procedure
Create procedure randomGenerate(@unit int,@min int,@max int)
as 
Begin
Declare @numbers table(number int)
Declare @i int = 0
Declare @number int
while (@i<@unit)
Begin
Set @number = floor(rand()*(@max-@min+1))+@min
if(not exists(Select * from @numbers where number = @number))
begin
insert into @numbers values(@number)
Set @i = @i + 1 
end
end
Select * from @numbers order by 1

End
//Run code : Execute randomGenerate 5,20,30

Trigger:
 A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.
Syntax:
create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body] 
Explanation of syntax:
create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
[before | after]: This specifies when the trigger will be executed.
{insert | update | delete}: This specifies the DML operation.
on [table_name]: This specifies the name of the table associated with the trigger.
[for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
[trigger_body]: This provides the operation to be performed as trigger is fired
BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.
Example:
Given Student Report Database, in which student marks assessment is recorded. In such schema, create a trigger so that the total and average of specified marks is automatically inserted whenever a record is insert.
Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.
Suppose the database Schema –
> desc Student; 

Field	Type	Null	Key	Default	Extra
Tid	Int	No	PRIMARY	NULL	Auto_increment
Name	Varchar(30)	Yes		NULL
Subj1	Int	Yes		NULL
Subj2	Int	Yes		NULL
Subj3	Int	Yes		NULL
Total	Int	Yes		NULL
per	int	Yes		NULL

 7 rows in set (0.00 sec)

SQL Trigger to problem statement.
create trigger stud_marks 
before INSERT 
on 
Student 
for each row 
set new.total = new.subj1 + new.subj2 + new.subj3, new.per = new.total * 60 / 100;

Above SQL statement will create a trigger in the student database in which whenever subjects marks are entered, before inserting this data into the database, trigger will compute those two values and insert with the entered values.
> insert into Student values(0, "ABCDE", 20, 20, 20, 0, 0); 
1 row affected
> select * from Student; 

Column	Values
Tid	1000
Name	Umes
Subj1	20
Subj2	30
Subj3	10
Total	60
per	30

1 row in set (0.00 sec)

In this way trigger can be creates and executed in the databases.