Programmable Objects in SQL Server, Slides of Database Management Systems (DBMS)

Download Programmable Objects in SQL Server and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Programmable Objects

Part 1

Programmable Objects in SQL server

• Sql server allows you to create three types of

programmable objects:

• Stored Procedures
• Triggers

Programmable Objects

• Variables

• Parameters

• Control flow constructs (IF/Else, while, for

loops)

Variables

• Sql server provides two types of variables:
• Local and Global
• Local variable is instantiated with single @ sign

while global variable is instantiated with double

@@ sign.

• Variables are declared with DECLARE clause

where you specify name and data type of

variable.

• Either SET or SELECT is used to assign a value to a

variable.

Control flow constructs

• If/else

• Declare @var int

• Set @var = 1

• If @var = 1

• Print ‘this is the code executed when true’

• Else

• Print ’this is the code executed when false’

While loop

Example: Stored Procedure with input

parameter

• Problem Statement: Create a stored procedure in SQL server that will take the student is as input parameter and display the CGPA of that particular student.
• Student( std-id, std-name, gpa)
• Create Procedure getstudentgpa (@std-id int)
• As
• Select std-name, gpa
• From Student
• Where std-id = @std-id
• Go
• Exec getstudentgpa 3;

Output parameter example

create procedure getstudentCGPA @studentID int, @CGPA int output AS select CGPA from student where studentID = @studentID go declare @var int Execute getstudentCGPA 12 , @CGPA= @var output

Practice Problem 2

• Create a stored procedure that takes ID of the student as input and display the grades of that student with course Name and student name. The procedure also sets the value of grade in student table for that particular student.
• Output should like: Student Name Course Name Grades Course-id Course-title 1 Database 2 Data structures 3 Operating systems 4 OOP 5 ICT Grades Null Null Null Null Null Null Null Null Null

create procedure studentinfo @studentname char(30) as begin declare @grade char(2) declare @cgpa int select @cgpa = (select cgpa from student1 where student_name = @studentname) if(@cgpa = 3) begin set @grade = 'B' end if (@cgpa = 2) begin set @grade = 'C' end update student set grade = @grade where student_name = @studentname select student_name,course_title,grade from student1 as s inner join course as c on s.course_id = c.course_id where s.student_name = @studentname end go

Resources

• www.1keydata.com

• Chapter 5 of book Database Management

Systems” Author: Gehrke, Ramakishnan