MSSQL Case

Here are some example of MSSQL Cases:

USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Other way:

USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO

Advertisements

MSSQL EXISTS and IF EXISTS

Using NULL in a subquery to still return a result set:

USE AdventureWorks ;
GO
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;

Comparing queries by using EXISTS and IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO

Usage of IN :

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO

Comparing queries by using EXISTS and = ANY


USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store s
WHERE EXISTS
(SELECT *
FROM Purchasing.Vendor v
WHERE s.Name = v.Name) ;
GO

Usage of ANY:

USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store s
WHERE s.Name = ANY
(SELECT v.Name
FROM Purchasing.Vendor v ) ;
GO

Comparing queries by using EXISTS and IN

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EXISTS
(SELECT *
FROM HumanResources.Department d
WHERE e.DepartmentID = d.DepartmentID
AND d.Name LIKE 'P%');
GO

Usage of IN :

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE DepartmentID IN
(SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%');
GO

Using NOT EXISTS

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department d
WHERE e.DepartmentID = d.DepartmentID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
GO

Advance Usage of Exists

If exists(
SELECT id FROM Table1 where price > = 1000
)
begin

SELECT '>= than 10000' as MSG
print '> than 10000' -- OR you can print as out put
return;


end