·         Advantages Of Stored Sub Programs compared to Independent SQL Statements

·         Stored Procedures

·         Creating , Altering and Dropping

·         Optional Parameters

·         Input and Output Parameters

A stored procedure is a collection of T-SQL statements. Let us suppose you are running a query number of time office. Every time you have to write in query and execute it. Instead of that, you can save/store the query in a stored procedure with a specific name and you can invoke the procedure by calling that name.

Using stored procedure user can do CREATE, UPDATE, INSERT and DELETE operations.

Syntax:

CREATE PROCEDURE/PROC <PROCEDURE_NAME>

@parameter1,2,… DATATYPE

AS

BEGIN

T-SQL statements1

T-SQL statements2

T-SQL statements3

T-SQL statements4

Etc…

END

Example:

CREATE PROCEDURE [dbo].[sp_Empinfo]

AS

BEGIN

     SELECT * FROM tbl_Employee

END

ALTER:

Alter PROCEDURE PROCEDURE/PROC <PROCEDURE_NAME>

@parameter1,2,… DATATYPE

AS

BEGIN

T-SQL statements1

T-SQL statements2

T-SQL statements3

T-SQL statements4

Etc…

END

Drop

DROP PROCEDURE sp_Stateinfo

Parameters/Input paramters:

Parameters are used to get specific data from a procedure. In general we will pass values to where condition in the query.  

Example:

CREATE PROCEDURE [dbo].[sp_Empinfo]

@empno Int

AS

BEGIN

     SELECT * FROM tbl_Employee WHERE Empno = @empno

END

Output parameters

Output parameters are used to catch the procedure result into a variable. Output parameters are declared with out keyword.

CREATE PROCEDURE [dbo].[sp_Empinfo]

@empno Int,

@count INT Out/Output

AS

BEGIN

     SELECT @count = count(*) FROM tbl_Employee WHERE Empno = @empno

END

To catch the output, we need to create a variable with the same data type, which we declared in the procedure as output parameter.

DECLARE @cnt int

EXEC sp_Empinfo @cnt OUT

select @cnt

Then @cnt will catch the result from the sp_Empinfo Procedure.

Optional parameters:

Optional parameters will contain default values. In case user not  supply any data to the variables in the procedure, using  the default values, output will be generated.

Example: I want to write a procedure to return employees from employee table. If you pass EmpID related employee information should return. If you not pass any parameter, all employee information should return.

CREATE PROCEDURE ved_Opionalprarameters

@empno VARCHAR(50) = null

AS

BEGIN

SELECT * FROM tbl_Employee WHERE EmpNo = @empno OR @empno IS NULL

END

In the above example I declared @empno as output parameter.

A stored procedure is a collection of T-SQL statements. Let us suppose you are running a query number of time office. Every time you have to write in query and execute it. Instead of that, you can save/store the query in a stored procedure with a specific name and you can invoke the procedure by calling that name.

Using stored procedure user can do CREATE, UPDATE, INSERT and DELETE operations. 

Syntax:

CREATE PROCEDURE/PROC <PROCEDURE_NAME>

@parameter1,2,… DATATYPE

AS

BEGIN

T-SQL statements1

T-SQL statements2

T-SQL statements3

T-SQL statements4

Etc…

END

Example:

CREATE PROCEDURE [dbo].[sp_Empinfo]

AS

BEGIN

     SELECT * FROM tbl_Employee

END

ALTER:

Alter PROCEDURE PROCEDURE/PROC <PROCEDURE_NAME>

@parameter1,2,… DATATYPE

AS

BEGIN

T-SQL statements1

T-SQL statements2

T-SQL statements3

T-SQL statements4

Etc…

END

Drop

DROP PROCEDURE sp_Stateinfo

Parameters/Input paramters:

Parameters are used to get specific data from a procedure. In general we will pass values to where condition in the query.  

Example:

CREATE PROCEDURE [dbo].[sp_Empinfo]

@empno Int  ---- Parameter

AS

BEGIN

     SELECT * FROM tbl_Employee WHERE Empno = @empno

END

Output parameters

Output parameters are used to catch the procedure result into a variable. Output parameters are declared with out keyword.

CREATE PROCEDURE [dbo].[sp_Empinfo]

@empno Int,

@count INT Out/Output

AS

BEGIN

     SELECT @count = count(*) FROM tbl_Employee WHERE Empno = @empno

END

To catch the output, we need to create a variable with the same data type, which we declared in the procedure as output parameter.

DECLARE @cnt int

EXEC sp_Empinfo @cnt OUT

select @cnt

Then @cnt will catch the result from the sp_Empinfo Procedure.

Optional parameters:

Optional parameters will contain default values. In case user not  supply any data to the variables in the procedure, using  the default values, output will be generated.

Example: I want to write a procedure to return employees from employee table. If you pass EmpID related employee information should return. If you not pass any parameter, all employee information should return.

CREATE PROCEDURE ved_Opionalprarameters

@empno VARCHAR(50) = null

AS

BEGIN

SELECT * FROM tbl_Employee WHERE EmpNo = @empno OR @empno IS NULL

END

In the above example I declared @empno as output parameter.

One - One Relationship:

In one to One relationship, one entity in one table can contain only one related entity in another table vice versa.

Let us suppose we have two tables. States information and related capital cities information.

CREATE TABLE dbo.States

(

Pk_State_Id INT IDENTITY PRIMARY KEY,

StateName VARCHAR(255)

);

CREATE TABLE dbo.Capitalcityinfo

(

Pk_CC_Id INT PRIMARY KEY,

CC_Name VARCHAR(255),

Fk_State_Id INT UNIQUE FOREIGN KEY REFERENCES dbo. States(Pk_State_Id)

);

INSERT INTO States VALUES('AP')

INSERT INTO States VALUES('TS')

INSERT INTO States VALUES('Kerala')

INSERT INTO States VALUES('Tamilnadu')

INSERT INTO Capitalcityinfo VALUES (1,'Amaravati',1)

INSERT INTO Capitalcityinfo VALUES (2,'Hyderabad',2)

INSERT INTO Capitalcityinfo VALUES (3,'Tiruvanantapuram',3)

INSERT INTO Capitalcityinfo VALUES (4,'Chennai',4)

SELECT * FROM States

SELECT * FROM Capitalcityinfo

Here we declared Fk_State_Id column in Capitalcityinfo table as Unique foreign key. So, it doesn’t let you to insert more than one value in that column. So, States table can contain only one related row information in Capitalcityinfo table. And in the same way Capitalcityinfo table contains only one related state information in States table.

This is called One-to-One relation.

 If you run Query as below, you can find the relation between the tables.

SELECT S.StateName,CP.CC_Name FROM States S LEFT JOIN Capitalcityinfo CP ON S.Pk_State_Id=CP.Fk_State_Id

One – To – Many Information:

In One-to-many relationship, one entity in a table can contains relation with more than one row in another table.

Let us suppose we have one more table, CityInfo.

CREATE TABLE dbo.CityInfo

(

Pk_City_Id INT PRIMARY KEY,

City_Name VARCHAR(255),

Fk_State_Id INT FOREIGN KEY REFERENCES dbo. States(Pk_State_Id)

);

This table contains list of city names of all states which are registered in states table. So, one state can contain more than one City information in Cityinfo table. But, if you take any city that contains only one related state information in states table.

INSERT INTO CityInfo VALUES(1,'Vjw',1)

INSERT INTO CityInfo VALUES(2,'Vizag',1)

INSERT INTO CityInfo VALUES(3,'Narasapuram',1)

INSERT INTO CityInfo VALUES(4,'Hyd',2)

INSERT INTO CityInfo VALUES(5,'Sec',2)

INSERT INTO CityInfo VALUES(6,'Warangal',2)

INSERT INTO CityInfo VALUES(7,'Kocchi',3)

INSERT INTO CityInfo VALUES(8,'Tirucchi',3)

INSERT INTO CityInfo VALUES(9,'Aleppi',3)

INSERT INTO CityInfo VALUES(10,'Madhurai',4)

INSERT INTO CityInfo VALUES(11,'Maadras',4)

INSERT INTO CityInfo VALUES(12,'Tiruvayyuru',4)

If you observe in the above list of cities and states, every state is having more than one related city information in Cityinfo table. But if you take any cityname from CityInfo table, that contains only one related State name in States table.

So here the two tables are in One-to-Many relation ship.

 If you run Query as below, you can find One-To-Many relation between the tables.

SELECT S.StateName,CI.City_Name FROM States S LEFT JOIN CityInfo CI ON S.Pk_State_Id=CI.Fk_State_Id

Many-to-Many relationship:

   In Many-to-Many relationship, one entity in a table can contain more than one related row information in second table and second table can contain more than one related rows in first table.

  Let us suppose we have 3 tables. Student and Course and one intermediate table Course_Student. Student table contains the information of all students. In the same way Courses table can contain information of All courses and “Course_Student” acts like a mediator and shows the relation between Student and Course tables.

CREATE TABLE Student(

StudentID INT PRIMARY KEY,

StuentName VARCHAR(100)

)

CREATE TABLE Course(

CourseID INT PRIMARY KEY,

CourseName VARCHAR(100)

)

CREATE TABLE Course_Student(

CourseID INT,

StudentID INT

)

INSERT INTO Student VALUES(1,'Sekhar')

INSERT INTO Student VALUES(2,'Bhanu')

INSERT INTO Student VALUES(3,'Naga')

INSERT INTO Course VALUES(1,'Dotnet')

INSERT INTO Course VALUES(2,'SQLServer')

INSERT INTO Course VALUES(3,'MVC')

INSERT INTO Course_Student VALUES(1,1)

INSERT INTO Course_Student VALUES(1,2)

INSERT INTO Course_Student VALUES(1,3)

INSERT INTO Course_Student VALUES(2,1)

INSERT INTO Course_Student VALUES(2,3)

INSERT INTO Course_Student VALUES(3,2)

INSERT INTO Course_Student VALUES(3,1)

Here if you observe the relations, we can find each student learning more than one course. In the same way, each course is having more than one student.

Many-to-Many relationship it is having.

If you run the below query, you can find that, each course is having more than one student and each student is learning more than one course

SELECT S.StuentName,C.CourseName FROM Student S LEFT JOIN Course_Student CS ON S.StudentID=CS.StudentID

LEFT JOIN Course C ON CS.CourseID=C.CourseID.

·         Constraints     

                                                         

                A Constraint is nothing but a rule. We can define rules to monitor data while inserting, deleting updating in a table.                                                                               

                                                                                               

                There are 6 Types of Constraints in SQL Server.                                                                            

                1.     Primary Key Constraint,                                                                       

                2.     Unique Key Constraint                                                                         

                3.     Foreign Key Constraint                                                                        

                4.     Not null Key Constraint                                                                        

                5.     Check key Constraint                                                                            

                6.     DEFAULT key Constraint                                                                      

               

Primary Key Constraint (PK) – Primary key constraint is to maintain uniqueness in the table. PK doesn’t allow duplicates and null values.

Unique Key Constraint – Unique key is also to maintain uniqueness in the records. But you can enter one null value into the table column.

Foreign Key Constraint(FK)) – Foreign key dependent on Primary key. It allowed entering the values which are accepted by the related Primary key.  FK is used to restrict the junk data.

Not null Key Constraint - Not null key is used to restrict null values enter into the columns.

Check Key Constraint – Check key is used to enter the validated data only into the table columns. While inserting data, we can check the value s per mentioned norms or not.

DEFAULT Key Constraint – Default Key is used to enter default values into the table column incase null values entered.                

                                                                                                                               

                Interview questions:                                     

                1              What is a Constraint?.                                                   

                2              How many constraints are there in SQL Server?