IF...ELSE
IF...ELSE allows you to conditionally execute a statement
| IF boolean_expression BEGIN sql statements END ELSE BEGIN sql statements END |
Example #1
use Northwind
/* IF Construct */
-- Check for the Existance of a row in a table
IF Exists ( SELECT * FROM Products WHERE ProductName = 'Chang')
BEGIN
PRINT 'The Product exists'
END
ELSE
BEGIN
PRINT 'The Product Does not Exists'
END
GO
Example #2
use Northwind
--Check for the Non Existance of a row in a table
IF Not Exists ( SELECT * FROM Products WHERE ProductName = 'Chang')
BEGIN
PRINT 'The Product exists'
END
ELSE
BEGIN
PRINT 'The Product Does not Exists'
END
GO
Example #3
use Northwind
-- To check for Units In Stock value in a Column of a Table
DECLARE @cost int
IF Exists (SELECT * FROM Products WHERE ProductName = 'Chai')
BEGIN
SELECT @cost =
(select UnitsInStock FROM Products WHERE ProductName = 'Chai')
IF @cost > 20
BEGIN
PRINT 'It is Costly '
END
ELSE
BEGIN
PRINT 'It is Moderate'
END
END
ELSE
BEGIN
PRINT ' Title Does not Exists'
END
Case
Allows SQL statement to be simplified for conditional values
Example #4
-- Usage of Case statements
DECLARE @price int
SET @price = 22
SELECT "Description" = Case
When @price >= 0 and @price <= 18 Then 'Minor ' + convert(char(3),@price)
When @price > 18 and @price <= 100 Then 'Major ' + convert(char(3),@price)
ELSE 'Not Valid'
END
GO
Example #5
use pubs
/* Using type in the When clause of the Case */
SELECT title_id,title,[type],
'Type of Book' = CASE
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'business' THEN 'Business'
WHEN type = 'popular_comp' THEN 'Popular Computing'
ELSE 'Not yet decided'
END,
price,
Advance
FROM titles
GO
-- or
use pubs
/* Using Type in the Case Clause and value in the When Clause */
SELECT
title_id,
title,
type,
'Type of Book' = CASE type
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'trad_cook' THEN 'Traditional Cooking'
WHEN 'psychology' THEN 'Psychology'
WHEN 'business' THEN 'Business'
WHEN 'popular_comp' THEN 'Popular Computing'
ELSE 'Not yet decided'
END,
price,
Advance
FROM titles
GO
0 التعليقات:
إرسال تعليق