SQL - IF statements

Refer Source Video: SQL - IF statements

************************
The Basic Syntax of IF:

************************
DECLARE @NumFilmsCategory1 INT
DECLARE @NumFilmsCategory2 INT

SET @NumFilmsCategory1 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 1)
SET @NumFilmsCategory2 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 2)

IF @NumFilmsCategory1 >0

BEGIN

PRINT 'message here'

PRINT 'message here'

IF @NumFilmsCategory2 >0

BEGIN

PRINT 'message here'

END

ELSE

BEGIN

PRINT 'message here'

END

END

ELSE

BEGIN

PRINT 'message here'

PRINT 'message here'

END

************************
Stored Procedure:
************************
USE Movies
Go

CREATE PROC spVariableData( @InfoType VARCHAR(9) )
AS
BEGIN

IF @InfoType='ALL'

BEGIN
(SELECT * FROM tblFilm)
RETURN
END

IF @InfoType='AWARD'

BEGIN
(SELECT FilmName FROM tblFilm)
RETURN
END

--if other than above ones

SELECT 'You must choose ALL or AWARD'
END

--
EXEC spVariableData @InfoType='ALL'
--
EXEC spVariableData @InfoType='AWARD'

************************************************************************
How to Use Variables, IF and CASE in Database Interactions with TransactSQL

SET NOCOUNT (Transact-SQL)

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.