SQL Paging

SQL Paging Select Query

SELECT *
  FROM (SELECT *
          FROM ( SELECT ROWNUM SNO
                      , RESULT.*
                   FROM (

SELECT DISTINCT PSD.PSD_SUMMARYID   PMD_SUMMARYID,
                                                  TO_CHAR(PSD.PSD_CREATEDTIME, :dateFormat) PSD_CREATEDTIME,
                                                  PSD.PSD_CREATEDTIME CREATEDTIME,
                                                  INS.INS_NAME  INSURERNAME                                             
                                               
                                    FROM [SCHEMA].PLC_PSD_PAYMENTSUMMARYDETAILS PSD
                                                INNER JOIN [SCHEMA].PLC_PMD_PAYMENTDETAILS PMD
                                                ON PSD.PSD_SUMMARYID = PMD.PMD_SUMMARYID
                                                INNER JOIN [SCHEMA].PLC_PLC_POLICY PLC
                                                ON PMD.PMD_POLICYID = PLC.PLC_POLICYID
                                                INNER JOIN [SCHEMA].MST_INS_INSURERMASTER INS
                                                ON INS.INS_INSURERID              = PSD.PSD_INSURERID
                                                WHERE PLC.PLC_APPLICATIONCLIENTKEY = :APPLICATIONCLIENTKEY
                                                AND PLC.PLC_DEALERID               = :PLC_DEALERID
                                                AND PMD.PMD_SUMMARYID IS NOT NULL
                                     order by PMD_SUMMARYID desc

) RESULT
                  WHERE ROWNUM <=(:Page_No*10) ORDER BY ROWNUM DESC )
         WHERE ROWNUM <=10 ORDER BY ROWNUM DESC
       )
 ORDER BY SNO

SQL Server Books

Books on SQL Server

Book1

Book2

SQL Server IF Exists

CheckIf Column Exists in SQL Server Table

Method 1

IFEXISTS(SELECT*FROMsys.columns

WHEREName=N'columnName'ANDOBJECT_ID=OBJECT_ID(N'tableName'))

BEGIN

PRINT'YourColumnExists'

END

For AdventureWorks sample database

IFEXISTS(SELECT*FROMsys.columns

WHERE Name = N'Name' AND OBJECT_ID =OBJECT_ID(N'[HumanResources].

[Department]'))

BEGIN

PRINT'YourColumnExists'

END

Method 2

IFCOL_LENGTH('table_name','column_name')ISNOTNULL

BEGIN

PRINT'YourColumnExists'

END

For AdventureWorks sample database

IFCOL_LENGTH('[HumanResources].[Department]','Name')ISNOTNULL

BEGIN

PRINT'YourColumnExists'

END

Method 3

IFEXISTS(

SELECTTOP1*

FROMINFORMATION_SCHEMA.COLUMNS

WHERE[TABLE_NAME]='TableName'

AND[COLUMN_NAME]='ColumnName'

AND[TABLE_SCHEMA]='SchemaName')

BEGIN

PRINT'YourColumnExists'

END

For AdventureWorks sample database

IFEXISTS(

SELECTTOP1*

FROMINFORMATION_SCHEMA.COLUMNS

WHERE[TABLE_NAME]='Department'

AND[COLUMN_NAME]='Name'

AND[TABLE_SCHEMA]='HumanResources')

BEGIN

PRINT'YourColumnExists'

END

SQL Examples

--1st table
create table community1234_832249(ID int IDENTITY (12,1) not null primary key,CName varchar(30) not null);
select * from community1234_832249
create proc comm1234_832249(@Name varchar(30),@ID int out)
as
begin
insert into community1234_832249(CName) values(@Name)
set @ID=@@identity
end
Declare @result int 
Execute comm1234_832249 'Microsoft',@ID=@result output
Execute comm1234_832249 'Java',@ID=@result output
Execute comm1234_832249 'BIPM',@ID=@result output
Execute comm1234_832249 'Learning & Development',@ID=@result output
print @result
select * from community1234_832249



--2nd tble
create table Employee123_832249(EmployeeID bigint primary key,Name varchar(30),Role varchar(30),Password varchar(30));
select * from Employee123_832249
create proc Emp_832249(@EmployeeID bigint,@Name varchar(30),@Role varchar(30),@Password varchar(30))
as
begin
insert into Employee123_832249 values(@EmployeeID,@Name,@Role,@Password)
end
Execute Emp_832249 402510,'Zacharia','HR','tcs@123'
Execute Emp_832249 520114,'Milow Neo J','LF','tcs@123'
Execute Emp_832249 320694,'Salli Ben','LF','tcs@123'
Execute Emp_832249 281354,'Mathew','HR','tcs@123'

select * from Employee123_832249



--3rd table
create table Employee_Community_Subject12_832249 (CommunityID int foreign key references  community123_832249(ID), 
EmployeeID bigint foreign key references Employee123_832249(EmployeeID) , Subject varchar(30), DateOfPosting datetime );
select * from Employee_Community_Subject12_832249 
create proc Emp_comm1_832249(@CommunityID int,@EmployeeID bigint,@Subject varchar(30),@DateOfPosting datetime)
as
begin
insert into Employee_Community_Subject12_832249  values(@CommunityID,@EmployeeID,@Subject,@DateOfPosting)
end
Execute Emp_comm1_832249 12,320694,'Microsoft & VSTO','08/12/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','09/13/13'
Execute Emp_comm1_832249 15,520114,'Self Discipline & Persistance','10/10/13'
Execute Emp_comm1_832249 13,320694,'Comapatibility with Java Swing','11/15/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','12/14/13'
select * from Employee_Community_Subject12_832249 



--Display the Employee & community details while validiting the login credentials using employeId & Passoword.
create proc create1234
(@Empid bigint,@password varchar(30))
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password='tcs@123')as D
on D.CommunityID=C.ID
end
 
 Execute create1234 320694,'tcs@123'

----Display the Employee details & Subject details for the posts from 13 sept 2013 to tilldate considering
 --different communities & subject line starting with M & ending with any charecter.

create proc abc12345
 as
 begin
 select D.CommunityID,D.Subject,D.DateofPosting,D.EmployeeID,C.CName from community1234_832249 as C inner join 
 (select S.CommunityID,S.Subject,S.DateofPosting,S.EmployeeID from  Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.Subject like 'M%' and S.DateofPosting between 
'09/13/14' and getDate()) as D on C.ID=D.CommunityID where C.CName like 'M%'
 end
 Execute abc12345

-- Select the Subject line details & Employee details of those who posted more than once,

  --on behalf of the Learning & Development Community in the sorting order of Date. 
alter proc xyz12
  as
  begin
 select E.EmployeeID,E.Name,D.CommunityID from Employee123_832249 as E 
inner join (select C.ID,C.CName,S.CommunityID, 
 S.EmployeeID  from community1234_832249 as C inner join Employee_Community_Subject12_832249 as S
on C.ID=S.CommunityID  group by S.EmployeeID,S.CommunityID,
C.ID,C.CName having count(S.EmployeeID)>1 ) as D on E.EmployeeID=D.EmployeeID
 where D.CName='Learning_and_Development'
 end
 Execute xyz12

--Display the Community detail with the Employee details who haven't posted anything.
create proc create123456
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.DateofPosting IS NULL)as D
on D.CommunityID=C.ID
end
Execute create123456

--search
create proc search1
(@EmployeeID bigint)
as
begin
select D.EmployeeID,E.Name,E.Role,D.DateofPosting,D.Subject from Employee123_832249 as E inner join (select S.EmployeeID,C.CName,S.Subject,S.DateofPosting from  community1234_832249 as C inner
join Employee_Community_Subject12_832249 as S on C.ID=S.CommunityID) as D on D.EmployeeID=E.EmployeeID where D.EmployeeID=@EmployeeID
end
Execute search1 320694

--modify
create proc modify1
(@empid bigint, @Name varchar(30),@Role varchar(30),@password varchar(30))
as
begin
update Employee123_832249 set Name=@Name,Role=@Role,Password=@password where EmployeeID=@empid
end

Execute modify1 320694,Swati,Lecturer,swati
select * from Employee123_832249

GETDATE() Returns the current date and time
NOW() Returns the current date and time
ALTER TABLE Persons
DROP COLUMN DateOfBirth
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
TRUNCATE TABLE table_name
DROP TABLE table_name
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;


SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;



SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

select distinct m.name from students s
inner join mentor_ralationships mr on mr.student_id=s.student_id
inner join mentors m on m.mentoir_id=mr.mentor_id
where s.teacher_id=1;


SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID   


extra
--1st table
create table community12341_832249(ID int primary key,CName varchar(30) not null);
select * from community1234_832249
create proc comm1234_832249(@Name varchar(30),@ID int out)
as
begin
insert into community1234_832249(CName) values(@Name)
set @ID=@@identity
end
Declare @result int 
Execute comm1234_832249 'Microsoft',@ID=@result output
Execute comm1234_832249 'Java',@ID=@result output
Execute comm1234_832249 'BIPM',@ID=@result output
Execute comm1234_832249 'Learning & Development',@ID=@result output
print @result
select * from community1234_832249

--2nd tble
create table Employee123_832249(EmployeeID bigint primary key,Name varchar(30),Role varchar(30),Password varchar(30));
select * from Employee123_832249
create proc Emp_832249(@EmployeeID bigint,@Name varchar(30),@Role varchar(30),@Password varchar(30))
as
begin
insert into Employee123_832249 values(@EmployeeID,@Name,@Role,@Password)
end
Execute Emp_832249 402510,'Zacharia','HR','tcs@123'
Execute Emp_832249 520114,'Milow Neo J','LF','tcs@123'
Execute Emp_832249 320694,'Salli Ben','LF','tcs@123'
Execute Emp_832249 281354,'Mathew','HR','tcs@123'

select * from Employee123_832249

--3rd table
create table Employee_Community_Subject12_832249 (CommunityID int foreign key references  community123_832249(ID), 
EmployeeID bigint foreign key references Employee123_832249(EmployeeID) , Subject varchar(30), DateOfPosting datetime );
select * from Employee_Community_Subject12_832249 
create proc Emp_comm1_832249(@CommunityID int,@EmployeeID bigint,@Subject varchar(30),@DateOfPosting datetime)
as
begin
insert into Employee_Community_Subject12_832249  values(@CommunityID,@EmployeeID,@Subject,@DateOfPosting)
end
Execute Emp_comm1_832249 12,320694,'Microsoft & VSTO','08/12/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','09/13/13'
Execute Emp_comm1_832249 15,520114,'Self Discipline & Persistance','10/10/13'
Execute Emp_comm1_832249 13,320694,'Comapatibility with Java Swing','11/15/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','12/14/13'
select * from Employee_Community_Subject12_832249 

--Display the Employee & community details while validiting the login credentials using employeId & Passoword.
/*create proc create12_swati
as
begin
select E.EmployeeID,E.Name,E.role,E.Password,R.CommunityID from Employee123_832249 as E inner join 
Employee_Community_Subject12_832249 as R on E.EmployeeID=R.EmployeeID
end
Execute create12_swati*/


create proc create1234
(@Empid bigint,@password varchar(30))
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password='tcs@123')as D
on D.CommunityID=C.ID
end
 
 Execute create1234 320694,'tcs@123'



/*select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 
as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=281354 and E.Password='tcs@123'

select * from Employee_Community_Subject12_832249;
select CName,ID from community1234_832249;


create proc create123
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join (select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password=@password)as D
on D.CommunityID=C.ID
end
Execute create123

create proc login1
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Role,E.Name from  Employee123_832249 as E 
where EmployeeID=@EmployeeID and Password=@Password
end
Execute login1 402510,'tcs@123'
create proc login12
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Role,C.Subject,C.DateOfPosting from  Employee_Community_Subject12_832249  as C inner join Employee123_832249 as E on C.EmployeeID=E.EmployeeID 
where E.EmployeeID=@EmployeeID and Password=@Password
end
Execute login12 402510,'tcs@123'
Execute login12 520114,'tcs@123'
Execute login12 281354,'tcs@123'

create proc login123
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where in (select C.ID from community123_832249 
as C on C.ID = R.CommunityID)
end
create proc login1233
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID inner join  community123_832249 
as C on C.ID = R.CommunityID where E.EmployeeID=@EmployeeID and Password=@Password 
end
Execute login1233 520114,'tcs@123'
Execute login1233 320694,'tcs@123'*/
--Display the Employee details & Subject details for the posts from 13 sept 2013 to tilldate considering
 --different communities & subject line starting with M & ending with any charecter.
 /*create proc Empp_1

 as
 begin
 select E.Name,E.Role,R.Subject from 
  Employee123_832249 as E inner join Employee_Community_Subject12_832249 as R on R.EmployeeID=E.EmployeeID 
  where R.Subject like 'M%' and R.DateofPosting>'09/13/13'
 end
 Execute Empp_1*/

 create proc abc12345
 as
 begin
 select D.CommunityID,D.Subject,D.DateofPosting,D.EmployeeID,C.CName from community1234_832249 as C inner join 
 (select S.CommunityID,S.Subject,S.DateofPosting,S.EmployeeID from  Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.Subject like 'M%' and S.DateofPosting between 
'09/13/14' and getDate()) as D on C.ID=D.CommunityID where C.CName like 'M%'
 end
 Execute abc12345


 -- Select the Subject line details & Employee details of those who posted more than once,
  --on behalf of the Learning & Development Community in the sorting order of Date. 
  
  create proc check12
  as 
  begin
  select count(R.EmployeeID),
  E.Name,E.Role,R.Subject from Employee123_832249 as E
  inner join Employee_Community_Subject12_832249 as R on R.EmployeeID=E.EmployeeID inner join 
  community123_832249 as C on C.ID=R.CommunityID
  where C.Name='Learning & Development'
  group by 
  having count(R.EmployeeID)>1
  end
 
  create proc check12
  as 
  begin
  select CommunityID,count(EmployeeID),
  from  Employee_Community_Subject12_832249 as R inner join community123_832249 as C on C.ID=R.CommunityID
  inner join 
  where C.CName='Learning & Development'
  group by CommunityID
  having count(EmployeeID) > 1
  end

  alter proc xyz12
  as
  begin
 select E.EmployeeID,E.Name,D.CommunityID from Employee123_832249 as E 
inner join (select C.ID,C.CName,S.CommunityID, 
 S.EmployeeID  from community1234_832249 as C inner join Employee_Community_Subject12_832249 as S
on C.ID=S.CommunityID  group by S.EmployeeID,S.CommunityID,
C.ID,C.CName having count(S.EmployeeID)>1 ) as D on E.EmployeeID=D.EmployeeID
 where D.CName='Learning_and_Development'
 end
 Execute xyz12

--Display the Community detail with the Employee details who haven't posted anything.

  create proc abc1
  as
  begin
  select C.Name,E.Name,E.Role,R.Subject from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID inner join  community123_832249 
as C on C.ID = R.CommunityID where R.DateofPosting=null
  end
  Execute abc1

create proc create123456
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.DateofPosting IS NULL)as D
on D.CommunityID=C.ID
end
Execute create123456

 
 

   --Create a console application for the Modification & Search of the Employees given in the above Employee Table. Search the Employee with the EmployeeId & display the details. Then Modify the
--employee record for the Relevant fields. 
create proc search
(@EmployeeID bigint)
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from  Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where E.EmployeeID=@EmployeeID
end
Execute search 281354


create proc modify
(@Name varchar(30),@Role varchar(30),@Subject varchar(30),@DateofPosting DateTime)
as
begin
update Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where E.Name=@NameE.Role=@Role, R.Subject=@Subject, R.DateofPosting=@DateofPosting
end
create proc search1
(@EmployeeID bigint)
as
begin
select D.EmployeeID,E.Name,E.Role,D.DateofPosting,D.Subject from Employee123_832249 as E inner join (select S.EmployeeID,C.CName,S.Subject,S.DateofPosting from  community1234_832249 as C inner
join Employee_Community_Subject12_832249 as S on C.ID=S.CommunityID) as D on D.EmployeeID=E.EmployeeID where D.EmployeeID=@EmployeeID
end
Execute search1 320694



create proc modify1
(@empid bigint, @Name varchar(30),@Role varchar(30),@password varchar(30))
as
begin
update Employee123_832249 set Name=@Name,Role=@Role,Password=@password where EmployeeID=@empid
end

Execute modify1 320694,Swati,Lecturer,swati
select * from Employee123_832249

VIEWS

Views

View is a virtual table. It can contain columns from one or more table .View appears just like a real table, with a set of named columns and rows of data. SQL creates the illusion like table we can even

insert,update,delete using views

Syntax to create view:

create view <NameofView>

As

--- write select query here

Syntax to execute view

select * from <NameOfView>

Eg. Following is the view that retrieves the employees of Admin department. View keyword is used when creating view.

Create View adminEmployees

As

Select * from Employees where DeparmentName ='Admin'

Executing the view

select * from adminEmployees

If a view is created from one table then it can also be used to insert,update,delete values to the table

Insert into adminEmployees

Values('Priya','5/5/2010','Admin')

Insert into adminEmployees

Values('Puja','5/15/2010','IS')

The above statement will insert value to Employee table.

Query 1:

Select name from Employee 

Result;

Priya

Puja

Query 2:

select name from adminEmployees

Result:

Priya

Explanation:

Employee table contains both the employee of all departments but the adminEmployee view 

displays Employee of admin department.

With Check Option:

If we use with check option when we create view, we can restrict modification to the table based 

on the condition specified in the view. That is insert/update/delete is based on the condition 

specified in the view.

Create View adminEmployees

As

Select * from Employees where DeparmentName ='Admin'

with check option

Reference Link

http://www.c-sharpcorner.com/Blogs/10575/advantages-and-disadvantages-of-views-in-sql-server.aspx

INDEXES

Indexes

Indexes are created on columns in tables. The index provides a fast way to look up data based on the values within those columns. There are two types of indexes: clustered index and non-clustered index. A Clustered index is the data of table sorted according to the selected columns .

A non- clustered index is just like the index of a book. It contains data sorted so that it’s easy to find, then once found, it points back to the actual page that contains the data. (In other words, it points back to the clustered index)

By default primary key column in a table is a clustered index

 CREATE INDEX indexname ON Table (attribute1);

 CREATE INDEX ci_ID ON Employee (ID);

Reference Link:

http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server

https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

STORED PROCEDURES

Stored procedures and its advantages

A stored procedure contains one or more SQL statements that you save so that you can reuse the code again. So if you have to write query again and again,create stored procedure and then execute it when ever required the stored procedure.You can call stored procedure from UI using ADO.Net which we will learn in the next section. You can have input and output parameters and Executes a series of SQL statements and return the query results.

Create and Execute stored procedures

Syntax for creating a procedure

CREATE PROCEDURE <Procedure_Name>

-- Add the parameters for the stored procedure here

<@Param1> <Datatype_For_Param1>,

<@Param2> <Datatype_For_Param2>

AS

BEGIN

 -- Write statements for procedure here

END

Syntax for executing a procedure

Execute Procedure_Name param1,param2

We can use Execute/Exec to execute a procedure followed by procedure. Provide parameters to procedures separated by commas. If the procedure does not have any parameter then use 

“Execute Procedure_Name” to execute a procedure. 

For example, if we have to retrieve name of the customer based on the phone number

SQL SELECT statement

SELECT CustomerName FROM Customer WHERE PhoneNumber = 9897969594;

Procedure is created to replace above query is as follows. Parameters specified where ever 

required.

CREATE PROCEDURE sp_GetName

@phoneNo varchar(10)

AS

begin

SELECT CustomerNameFROM Customer

WHERE PhoneNumber = @phoneNo

end

Explanation:

Procedure name is sp_GetName and input parameter to procedure is @phoneNo. All input 

parameters/local variables in SQL Server start with “@”. You can pass one to many parameters 

to procedures. Parameters have to be separated by commas

Run the stored procedure:

Stored procedure is executed by execute followed by procedure name and the required input 

parameters

Following is another sample which is used to update contact number and location of an 

employee by id

create proc sp_updateCustomer

(@contactNo bigint,

@location varchar(30),

@id int)

as

begin

update tblCustomer

set contactNo=@contactNo,Location=@location

where customerid=@id

end

Run the stored procedure:

In the above statements we have passed values for contact number, location and id when we execute the procedure. So the required details are updated. You can execute the same procedure with different values which enables you to reuse the procedure.

EXECUTE sp_GetName 9897675412

EXEC sp_updateCustomer 9898978790,'Delhi',5

Alter and drop stored procedure

We can alter a stored procedures by simply changing the required query and replacing create by alter keyword in a procedure.

For eg. Following is the procedure that is view customers

create proc sp_viewCustomer

select * from tblCustomer

Now there is a change in the requirement that we have to display the name and location of the 

customer. Then just do the following

alter proc sp_viewCustomer

select name,location from tblCustomer

Above procedure is modified in a way that it retrieves only name and location of the customer

Now if we have to remove a procedure permanently, we will use the following syntax

drop proc <Procedure_Name>

eg.

drop proc sp_viewCustomer

Output Parameters in Stored procedures

Output parameter is used in stored procedure to return a value to user. out keyword is used for output parameters. Value should be assigned to the parameter within the procedure.

Scenario: To insert customer to customer table and return the auto generated id.

create proc sp_insertCustomer

(@name varchar(30),

@contactNo bigint,

@location varchar(30),

@id int out)

insert into tblCustomer

values(@name,@contactNo,@location)

set @id=@@identity

Executing a procedure having output parameter

Declare @result int

Execute sp_insertCustomer 'Asha',9898989898,'Mumbai',@id= @result output

Print @result

Explanation:

set @id=@@identity - set keyword is used to do assignment in SQL. @@identity is a global variable which is used to retrieve the auto generated values. It should be used next to an insert statement.

Reference Links

 http://technet.microsoft.com/en-us/library/aa214299(v=sql.80).aspx

 http://msdn.microsoft.com/en-IN/library/ms345415.aspx

 http://msdn.microsoft.com/en-IN/library/ms188927.aspx

SQL SERVER JOINS

Introduction

SQL joins are used to relate information from different tables. SQL join is used in WHERE clause

of SELECT, UPDATE and DELETE statements.

Various types of joins available in SQL are:

 Inner

 Outer

◦ Left

◦ Right

◦ Full

 Cross Join

 Self Join

Inner Join

The INNER JOIN creates a new result table by combining column values of two tables (table1

and table2) based upon the join-predicate. The query compares each row of table1 with each row

of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is

satisfied, column values for each matched pair of rows of A and B are combined into a result row.

The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_field = table2.common_field;

Consider the following two tables, (a) CUSTOMERS table is as follows:

Left Outer Join

The SQL LEFT OUTER JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.This means that a left outer join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

The basic syntax of LEFT OUTER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

LEFT OUTER JOIN table2

ON table1.common_field = table2.common_field;

Here given condition could be any given expression based on your requirement.

Consider the following two tables, (a) CUSTOMERS table is as follows:

Right Outer Join

The SQL RIGHT OUTER JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

The basic syntax of RIGHT OUTER JOIN is as follows:

SELECT table1.column1, table2.column2...

RIGHT OUTER JOIN table2

ON table1.common_field = table2.common_field;

Consider the following two tables, (a) CUSTOMERS table is as follows:

Full Outer Join

The SQL FULL OUTER JOIN combines the results of both left and right outer joins.The joined table will contain all records from both tables, and fill in NULLs for missing matches on

The basic syntax of FULL OUTER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

FULL OUTER JOIN table2

ON table1.common_field = table2.common_field;

Here given condition could be any given expression based on your requirement.

Consider the following two tables, (a) CUSTOMERS table is as follows:

Cross Join

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from 

the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

The basic syntax of CROSS JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1, table2 [, table3 ]

Consider the following two tables, (a) CUSTOMERS table is as follows:

Self Join

The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

The basic syntax of SELF JOIN is as follows:

SELECT a.column_name, b.column_name...

FROM table1 a, table1 b

WHERE a.common_field = b.common_field;

Here, WHERE clause could be any given expression based on your requirement.

Consider the following two tables, (a) CUSTOMERS table is as follows:

SQL SUBQUERY

Introduction

• Subquery or Inner query or Nested query is a query in a query.
• SQL subquery is usually added in the WHERE Clause of the SQL statement.
• Generally we use subquery when we know how to search for a value using a SELECT statement, but do not know the exact value in the database.
• Subqueries are an alternate way of returning data from multiple tables.
• Subqueries can be used with the following SQL statements along with the comparision operators like =, <, >, >=, <= etc.

◦ SELECT

◦ INSERT

◦ UPDATE

◦ DELETE

Using Subquery in SQL Query :

Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below:

SELECT id, first_name

FROM student_details

WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id's you need to write the query in this manner:

SELECT id, first_name

FROM student_details

WHERE first_name IN (SELECT first_name

FROM student_details

WHERE subject= 'Science');

In the above sql statement, first the inner query is processed first and then the outer query is processed.

Using Subquery in insert statement:

Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study 

Maths in a table 'maths_group'.

INSERT INTO maths_group(id, name)

SELECT id, first_name || ' ' || last_name

FROM student_details WHERE subject= 'Maths'

Using Subquery in select statement:

A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from 

order_items where product_id = 101) as order_id from product p where 

p.product_id = 101;

Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p

WHERE p.product_id = (SELECT o.product_id FROM order_items o

WHERE o.product_id = p.product_id);

AGGREGATION OF INFORMATION

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

AVG() - Returns the average value

COUNT() - Returns the number of rows

FIRST() - Returns the first value

LAST() - Returns the last value

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

Group By Clause

The GROUP BY statement is used in conjunction with the aggregate functions to group

the result-set by one or more columns.

Syntax:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

Example:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders LEFT JOIN Shippers

ON Orders.ShipperID=Shippers.ShipperID

GROUP BY ShipperName;

The above SQL statement counts as orders grouped by shippers

Having Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used 

with aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

Example:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders INNER JOIN Employees

ON Orders.EmployeeID=Employees.EmployeeID

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 10;

The above SQL statement finds if any of the employees has registered more than 10 orders:

Problem Scenario

Write a query to get the employees who has orders more than 25 and their last name should be 

either 'Davolio' or 'Fuller'

Approach to solve the problem

Join Employee and Order Table use the aggregate function Count to find the order placed by the 

employee and use having clause to filter for the number of order that are more than 25 and use 

where clause for filtering based on the last name.

Solution

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID

WHERE LastName='Davolio' OR LastName='Fuller'

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 25;

Explanation about the solution

Employee and Order table is joined using the EmployeeID foreign key in the order table (join 

condition here). Then restrict the results only for the employee whose last name is 'Davolio' or 

'Fuller' using a where clause. Then restrict the count of order place using a having clause. Then 

we have grouped by the last name.

INSERT/UPDATE/DELETE/SELECT into Table SQL

Insert
 Insert statements are used to insert data into tables.

 Syntax

 INSERT INTO Table VALUES( “attribute value1”, “attribute value 2”....);
 INSERT INTO Table(Coumn1,Column2,..) VALUES( “attribute value1”, “attribute
value 2”....);

 Example

 Insert into Customer VALUES(1,'Customer Name1','Male',40,'India')
 Insert into Customer(CustomerID,CustomerName,Gender,Age,Location)
VALUES(1,'Customer Name1','Male',40,'India')


 Update

 UPDATE statement helps in modifying database records

Syntax

 UPDATE Table set attribute2 = “new value” WHERE attribute1 = “xyz”;

 Example

 Update Customer set age=30 where CustomerID=100


Delete

 Delete statement helps in deleting database records

Syntax
 DELETE FROM Table WHERE attribute1 = “xyz”;

Example
 Delete from Customer where CustomerID=100;

Select

Select statements help in selecting data from the database

 Syntax
 SELECT * FROM Table ;

 Example
 SELECT * FROM Customer

 Data can be retrieved from the database using different Select options which have been
discussed below

Select statement with projections 

 Selecting only particular columns from the database

 Syntax
 SELECT attribute1,attribute2 FROM Table ;

 Example
 SELECT CustomerID,CustomerName from Customer

Select statement with Distinct

 Shows the column values by removing the duplicate values.

 Syntax
 SELECT DISTINCT attribute1 FROM Table ;

 Example
 Select distinct Location from Customer

Select statement with String Functions

 String functions such as Upper,Lower,Substring,CharIndex can be used in the Select statements

 Syntax
 SELECT UPPER( attribute1), LOWER( attribute1) FROM Table;

Example
SELECT UPPER(Location) from Customer

Syntax
SELECT SUBSTRING(attribute1, 1, CHARINDEX(' ', attribute1)) FROM Table;

The below example will retrieve the first name from the Customer Name in which the First and the Last Name are separated by spaces.

Select SUBSTRING(CustomerName,1, CHARINDEX(' ', CustomerName)) from Customer

 The CharIndex function returns the position of the space character in Customer  Name. Then using the Substring function we can retrieve the first name by providing the  start position and the position returned by CharIndex function.

Select statement with Convert Functions

 The Convert function is sued to display the date columns in the table in different  styles

 Syntax
 SELECT CONVERT(data type, attribute1, format ) FROM Table;

 Example
 The below statement will display the DateOfBirth in mm/dd/yy format.

 Select Convert(varchar(10),DOB,101) from Customer

Select statement with Filters

 Select statements with Filters are used to select records based on condition

SELECT statement with WHERE

 The filter condition is specified using the WHERE Clause.
Syntax

 SELECT attribute1 FROM Table WHERE attribute2 = “xyz” ;
 SELECT attribute1 FROM Table WHERE attribute2 = “xyz” AND attribute3 = “pqr”;

Example
 Select CustomerName from Customer where CustomerID > 100;
 Select CustomerName from Customer where CustomerID > 100 and CustomerID <200;

 SELECT statement with WHERE and IN/NOT IN

 This filter is used for checking for matching and unmatched records given a list of values

Syntax

 SELECT attribute1 FROM Table WHERE attribute2 IN (“pqr”, “xyz”) ;
 SELECT attribute1 FROM Table WHERE attribute2 NOT IN (“pqr”, “xyz”) ;

 Example

 Select CustomerID,CustomerName from Customer Where Location in ('India','US')

 Displays the Customer details whose Location is either India or US.

 Select CustomerID,CustomerName from Customer Where Location NOT in ('India','US')

 Displays the Customer details whose Location is neither India or US.

SELECT statement with WHERE and NULL

This filter is used for selecting records for columns with NULL value.

Syntax

 SELECT attribute1 FROM Table WHERE attribute2 IS NULL ;

 Example

 Select CustomerID,CustomerName from Customer where Location is NULL

 Displays the Customer details whose Location is NULL.

SELECT statement with Order By

 This filter is used to display the records based on column values either in  ascending or descending order

Syntax

SELECT attribute1 FROM Table ORDER BY attribute1;
 SELECT attribute1 FROM Table ORDER BY attribute1 order by desc.

 Example

 Select CustomerID,CustomerName from Customer order by Location.
 Select CustomerID,CustomerName from Customer order by Location desc

SELECT statement with Group By

This filter is used for grouping records based on a column value. The column values used for grouping can be listed using the select statement.

Syntax

SELECT attribute1 FROM Table GROUP BY attribute1;

 Example

 SELECT Location FROM Customer GROUP BY Location;

 In the above example the records in Customer table will be grouped by location values. Suppose we have locations like India,US,UK, the records will be now grouped under  US,UK and India. The column values used for grouping alone can be displayed using  Select. So the above statement will display only US,UK and India.

SELECT statement with Group By and Having

The Having clause is used for displaying the column values used in group by clause based on a condition. In the above example if we want to display the locations which are having a count of more than 10 employees,then we can go for having clause. Having clause has to be used along with aggregate functions. Refer to Aggregate functions documents for understanding on Aggregate functions.

Syntax

SELECT attribute1 FROM Table GROUP BY attribute1 HAVING aggregate_function(attribute1) > value ;

 Example

 SELECT Location FROM Customer GROUP BY Location having  count(Location) > 0

 The aggregate function used is the count function.

 Problem Scenario

 *Insert values into Employee table which has Columns like EmployeeID,Employee Name, Department,Date of Joining.

 *Update the Employee name and Department of a particular Employee

* Delete an Employee based on Employee Id

* Select the Employee based on below criteria
 1.Employees of a Department
 2.Employees not assigned to department
 3.Employees count grouped by department

Approach to solve the problem

 1.Insert the values into Employee table using Insert statement
 2.Update the Department and Employee name using update statement
 3.Delete an Employee using the delete statement by providing the Employeeid in the
 where condition
 4.List the employees of a department using select with where condition
 5.List the employees who have not been assigned to department using null condition
 6.List the count of employees using GroupBy

Solution

 Insert into Employee(1, 'Emp1','Finance','09-09-2000')
 Update Employee set EmpName='Emp2',Department='Accounts' where Empid=2
 Delete from Employee where Empid= 100
 Select * from Employee where Department='Accounts'
 Select * from Employee where Department is null
 Select count(*) from Employee group by Department

Explanation about the solution

 1.Details are inserted using Insert statement.
 2.The Department and EmpName has been updated using update statement based on  Empid
 3.An employee with empid is deleted using delete statements
 4.The employees of Department Account have been listed
 5.The employees not assigned to any department have been listed using null condition
 6.The count of employees of each department is displayed using group by and aggregate  function

Constraints of SQL Table

Constraints helps to ensure that valid data goes into the database entity by specifying rules.

- NOT NULL : Ensures that attribute cannot be null.

 - Primary Key : Represents a column or combination of columns as a unique identity for a row

 - FOREIGN KEY : Used to establish relationships between tables using matching columns.

 - UNIQUE : The value is unique (not repeated for other entities)

 - Check : Used to allow values for a column based on a condition

 - Default : Used for inserting default value into columns.

NOT NULL
 The NOT NULL constraint will not allow a column to accept NULL values

 Below is an example for creating NOT NULL constraint.

 Create table Customer(CustomerID int NOT NULL,Name varchar(50) NOT
NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))

Primary Key
 The PRIMARY KEY constraint identifies each record in a database table by allowing only unique values. Each table can have only ONE primary key

 Below is an example for creating Primary Key constraint.

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))

 Primary Key can also be added using alter statement as below

                                       ALTER TABLE Customer
                                       ADD PRIMARY KEY (CustomerID)

Foreign Key

 Foreign Key is used to establish relationships between tables using matching columns. A FOREIGN KEY in one table refers to a PRIMARY KEY in another table. It ensures that data available in the Primary key column of one table can only be entered in the Foreign key column of another table.

 Below is an example for creating Foreign Key constraint

 CREATE TABLE Orders
 OrderId int NOT NULL PRIMARY KEY,
 OrderNo int NOT NULL,
 CustomerId int FOREIGN KEY REFERENCES Customer(CustomerID)


 Foreign Key can also be added using alter statement as below

 ALTER TABLE Orders
 ADD FOREIGN KEY (CustomerId)
 REFERENCES Customer(CustomerId)

UNIQUE
 UNIQUE Constraint like Primary Key ensures uniqueness for a column. The UNIQUE Constraint is different from Primary Key in that a table can have only Primary Key but there can be many columns with Unique Constraint.

 Below is an example for creating Unique Constraint

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Contact int UNIQUE)


Check
 Check constraint is used to allow values for a column based on a condition.

 Below is an example for creating Check Constraint

 We can create a Check Constraint on Age column to allow only values greater than 0.

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),CHECK (Age >0))


Default
 Default constraint is used for inserting default value into columns.

 Below is an example for creating Default Constraint

 We can create a Default Constraint on Location column to enter the default location as 'India' if location is not inserted while entering Customer details.

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Location varchar(20) DEFAULT 'India')


Problem Scenario:
 Create database tables for adding employee details such as Employeeid, Employee name,Date of Joining,Age,Contact number and their salary details such as  HRA,Basic,PF,Bonus and Gross Salary on monthly basis.

Approach to solve the problem
 1. As we need to insert Employee details and their salary details on monthly basis we can  create one table for Employee details and one table for storing Employee Salary details
 2. Create the table Employee with Employeeid,Employee name,Date of Joining, Age,Contact number columns.
 3.Create the table EmployeeSalary with HRA,Basic,PF,Bonus and Gross
 Salary,SalaryDate columns.


Solution
 Create table Employee(EmployeeID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,DateOfJoining Date,Contact
int UNIQUE,CHECK (Age >0))

 Create Table EmployeeSalary(SalaryID int NOT NULL Primary Key,Basic int,HRA
int,Bonus int,Gross int,SalaryDate Date,EmployeeID FOREIGN KEY REFERENCES
Employee(EmployeeID))

Explanation about the solution
 1. As the Employee Id should be unique and identify each row separately we can create
 Employee Id as the Primary Key in Employee table.
 2. We can create a Check Constraint on age and Unique Constraint on Contact in
 Employee table.
 3. In Employee salary details create Salary Id as Primary Key and Employee ID as foreign
 key referencing Employee Id in Employee table.

Alter Table SQL

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE also comes under Data Definition language (DDL)

Add Column
Syntax:

                                               ALTER TABLE table_name

                                                ADD column_name datatype

 

 Example

 

                                                ALTER TABLE Customer

                                                ADD Location varchar(50);

 

Drop Column

 Syntax

 

                                             ALTER TABLE table_name

                                             DROP COLUMN column_name

 

 Example

 

                                            ALTER TABLE Customer

                                             Drop Location varchar(50);

 

Change DataType

Syntax

 

                                           ALTER TABLE table_name

                                           ALTER COLUMN column_name datatype

 Example

 

                                            ALTER TABLE Customer

                                            Drop Location varchar(30);

 

Create Table SQL

Table Creation

Entities are represented as tables in database. The attributes of entities are represented as columns. As the data’s are stored in the attributes of entities the data’s are stored in columns in tables. Tables are organized into rows and columns.

 

 For example the Customer entity can be represented as Customer table and the attributes like Name,Age,Gender,Occ,Phone,Location,Work Exp can be represented as

columns in the table.

 

Create Table

The CREATE TABLE statement is used to create a table in a database. Create table

statement comes under Data Definition language (DDL)

 

Syntax

 

CREATE TABLE Table ( Column1 Data type(size), Column2 Data type(size),....);

 

While creating table we need to

 Specify the table name

 Specify the column names

 Specify what type of data each column need to hold (e.g. varchar, integer, decimal, date,

etc.).

 Specify the size parameter,which the maximum length of the column of the table

 

Example

 

Create table Customer(CustomerID int,Name varchar(50),Age int,Gender

varchar(10),Occ varchar(10));

 

 

DB SearchString SQL

Search entire database of SQL Server with the search string and it will return you the table name from the database and column name containing the string value

Here is the code:

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'umesh kumar'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM [Schema Name].' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
            BEGIN TRY
        EXECUTE(@sql_string)
            END TRY
            BEGIN CATCH
            END CATCH
        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur