Example 1)The procedure that adds the data to the student table
|
1 2 3 4 5 6 7 8 9 10 |
Create Procedure Sp_AddStudent( @name varchar(20), @surname varchar(20), @birthdate date, @gender char(1), @class varchar(5)) as Begin Insert Into Students(name,surname,birthdate,gender,class,point) values(@name,@surname,@birthdate,@gender,@class,0) End |
After running the code above, than if you get successful message you can run the code below.
|
1 |
Execute Sp_AddStudent 'Jane', 'Black','05.05.2005','F','9A' --Adds the student with given parameters |
Result: 1 row affected
Example 2) The procedure adds the book with given parameters to the books table. The books pageCount must be over then 100
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Create Procedure Sp_AddBook2( @name varchar(90), @pagecount int, @point int, @authorId int, @typeId int, @errorMessage varchar(1000) output) as Begin If(@pagecount>100) Begin INSERT INTO books (name,pagecount,point,authorId,typeId) VALUES(@name,@pagecount,@point,@authorId,@typeId) Set @errorMessage = 'Successful' End Else Begin Set @errorMessage = 'Error:The book''s PageCount is smaller than 100' End End |
After running the code above, than if you get successful message you can run the code below.
|
1 |
Execute Sp_AddBook2 'Three Soldiers',50,20,3,2 |
Output: “Error:The book’s PageCount is smaller than 100”
|
1 |
Execute Sp_AddBook2 'Three Soldiers',150,20,3,2 |
Output: “Successful”
Example 3) The procedure that deletes student by studentId
|
1 2 3 4 5 |
Create Procedure Sp_DeleteStudentById( @StudentId int) as Begin Delete From students where studentId = @StudentId End |
After running the code above, than if you get successful message you can run the code below.
|
1 |
Execute Sp_DeleteStudentById 10 --Deletes the student whose student number is 10 |
Result: 1 row affected
Example 4)The procedure that updates data by studentId on the student table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Create Procedure Sp_UpdateStudent( @name varchar(20), @surname varchar(20), @birthdate date, @gender char(1), @class varchar(5), @point int, @StudentId int) as Begin Update students set name=@name, surname= @surname, birthdate = @birthdate, gender = @gender, class = @class, point = @point where studentId = @StudentId End |
After running the code above, than if you get successful message you can run the code below.
|
1 |
Execute Sp_UpdateStudent 'Jane', 'Black','05.05.2005','F','9A',10,10 |
Result: 1 row affected
