VARIABLES:

DECLARE @INPUT1 INT = 10; -- DEFAULT VALUE

SELECT @INPUT1 as INPUT1

DECLARE @INPUT1 INT, -- Define the variable INPUT1

@INPUT2 INT; -- You can define the next variable using comma

SET @INPUT1 = 10 -- Assign Value to the Variable

SET @INPUT2 = 20 -- Assign Value to the Variable

SELECT (@INPUT1 + @INPUT2) as OutputValue

-- For numerical values, the mathematical output is displayed

SELECT (@INPUT1 * @INPUT2) as OutputValue

-- For numerical values, the mathematical output is displayed

DECLARE @INPUT3 VARCHAR(10),

@INPUT4 VARCHAR(10);

SET @INPUT3 = 'ABC'

SET @INPUT4 = 'XYZ'

SELECT (@INPUT3 + @INPUT4) as OutputValue

-- For string, the values are concatenated

SELECT (@INPUT3 + ' ' + @INPUT4) as OutputValue

-- To add extra characters, use '+'

SELECT 'INPUT1: ' + CAST(@INPUT1 as VARCHAR) as OutputValue

-- If you are using a combination of String and Integer,
-- you will have to cast/convert the integer to varchar.

DECLARE @INPUT5 VARCHAR(10),

@INPUT6 VARCHAR(10);

SET @INPUT5 = '10'

SET @INPUT6 = '20'

SELECT (@INPUT5 + @INPUT6) as OutputValue

-- For string, the values are concatenated
-- notice how the behavior changes for integers and strings.

SELECT GETDATE() AS DATETIME INTO #TEMP1

SELECT * FROM #TEMP1

DECLARE @VAR1 VARCHAR(30)

SELECT @VAR1 = DATETIME FROM #TEMP1 -- ASSIGN COLUMN VALUES TO A VARIABLE

SELECT @VAR1 AS DATETIME

IF ELSE CONDITION:

DECLARE @INPUT1 INT, -- Define the variable INPUT1

@INPUT2 INT; -- You can define the next variable using comma

SET @INPUT1 = 10 -- Assign Value to the Variable

SET @INPUT2 = 20 -- Assign Value to the Variable

---- If condition with single line in action

IF @INPUT1 > 5
PRINT 'Input is greater than 5.'

---- If condition with multiple lines in action

IF @INPUT1 > 5
PRINT 'Input is greater than 5.'
PRINT 'Input value is: ' + CAST(@INPUT1 as VARCHAR)

---- If else if condition

IF @INPUT1 < 5
BEGIN
PRINT 'Input is greater than 5.'
PRINT 'Input value is: ' + CAST(@INPUT1 as VARCHAR)
END
ELSE
IF @INPUT2 > 15
PRINT 'Input is greater than 15'

---- nested if else condition

IF @INPUT1 < 5
BEGIN -- BEGIN is needed because of multiple lines in IF
condition
PRINT 'Input is greater than 5.'
PRINT 'Input value is: ' + CAST(@INPUT1 as VARCHAR)
END
ELSE
IF @INPUT2 < 15
PRINT 'Input is greater than 15' -- BEGIN not needed
ELSE
IF @INPUT2 = 20
PRINT 'Input is 20'
WHILE LOOP:

DECLARE @VAR1 INT

SET @VAR1 = 1

-- Simple WHILE loop

WHILE (@VAR1 < 10)
BEGIN
PRINT @VAR1
SET @VAR1 = @VAR1 + 1
END

-- WHILE loop with BREAK and CONTINUE in IF..ELSE condition

SET @VAR1 = 1

WHILE (@VAR1 < 10)

BEGIN
PRINT @VAR1
SET @VAR1 = @VAR1 + 1

IF (@VAR1 % 5) = 0
BEGIN
PRINT 'Divisible by 5'
BREAK
END
ELSE
CONTINUE
END
CTE: COMMON TABLE EXPRESSION

WITH CTE_DATETIME (DateNow,TimeNow)

AS (
SELECT CONVERT(VARCHAR,GETDATE(),103) AS CurrDate ,
CONVERT(VARCHAR,GETDATE(),24) AS CurrTime
)

----SELECT CONVERT(VARCHAR,GETDATE(),20) AS CurrDateTime

SELECT DateNow , TimeNow

FROM CTE_DATETIME

--IF ( SELECT DateNow from CTE_DATETIME ) < GETDATE()

-- PRINT 'HERE'

--SELECT DateNow
--FROM CTE_DATETIME
--WHERE DateNow <= CONVERT(VARCHAR,GETDATE(),103)

--Syntax:

WITH <CTENAME> (<Col1>,<Col2>,...,<coln>)

AS
(
select <xCol1>,<xCol2>,...,<xColn>
from <table>
)

select <Col1>,<Col2>,...,<coln>
from <CTENAME>
TRY – CATCH

BEGIN TRY
DECLARE @out DECIMAL(7,2)

SET @out = 1/1

--SET @out = 1/0
select @out as OutValue

END TRY

BEGIN CATCH
select ERROR_MESSAGE() as ErrorMessage
END CATCH
PARAMETERIZED SCALAR FUNCTION

CREATE FUNCTION fn_calcAdd (@VAR1 DECIMAL(7,2))

RETURNS DECIMAL(7,2)
BEGIN
RETURN @VAR1 + @VAR1
END

DECLARE @OUTPUT DECIMAL(8,2)

SET @OUTPUT= MobInv.dbo.fn_calcAdd (15.6)

SELECT @OUTPUT as outputValue

CREATE FUNCTION fn_calcAddTwoVars (@VAR1 DECIMAL(7,2),@VAR2 INT)

RETURNS DECIMAL(7,2)
BEGIN
RETURN @VAR1 + @VAR2
END

DECLARE @OUTPUT DECIMAL(8,2)

SET @OUTPUT= MobInv.dbo.fn_calcAddTwoVars (15.6,25)

SELECT @OUTPUT as outputValue

CREATE FUNCTION fnCheckDivisibility (@numerator decimal(7,2) , @denominator

decimal(7,2))
RETURNS VARCHAR(200)
BEGIN
DECLARE @out VARCHAR(200)
IF ((@numerator%@denominator)=0)
BEGIN
SET @out = cast(@numerator as varchar(10)) + ' is divisible by ' +
cast(@denominator as varchar(10))
END

ELSE
BEGIN
SET @out = cast(@numerator as varchar(10)) + ' is not divisible by '
+ cast(@denominator as varchar(10))
END

RETURN @out
END

DECLARE @n1 decimal(7,2)=12.0,

@n2 decimal(7,2)=13.0,
@outValue VARCHAR(200)

SET @outValue = StoreInvDetails.dbo.fnCheckDivisibility(@n1,@n2)

SELECT @outValue as OutValue

TRIGGER – INSERT

CREATE TRIGGER TrMobStoreIns

ON MobileStoreInventory
FOR INSERT
AS
BEGIN
DECLARE @log VARCHAR(100)

SET @log = 'Data has been pushed'

INSERT INTO LogTable

SELECT @log

END

CREATE TRIGGER TrMobStoreIns

ON MobileStoreInventory
FOR INSERT
AS
BEGIN
DECLARE @log VARCHAR(100),
@ModelNo VARCHAR(20);

SELECT @ModelNo = ModelNo from inserted

SET @log = 'Data has been pushed for '+ @ModelNo

INSERT INTO LogTable

SELECT @log

END

select * from LogTable

----delete from LogTable
----drop table LogTable
TRIGGER – DELETE

CREATE TRIGGER TrMobStoreDel

ON MobileStoreInventory
FOR DELETE
AS
BEGIN
DECLARE @ModelNo VARCHAR(20);

SELECT @ModelNo = ModelNo from Deleted

INSERT INTO LogTable

SELECT 'The Model No ' + @ModelNo + ' has been deleted.'
END
CURSOR

DECLARE <CursorName> CURSOR FOR

SELECT <column1> , <colmn2>
FROM <Table1>

DECLARE <varCol1> VARCHAR(20),

<varCol2> VARCHAR(20);

OPEN <CursorName>
FETCH NEXT FROM <CursorName> INTO <varCol1>, <varCol2>

WHILE @@FETCH_STATUS = 0
BEGIN
print <varCol1> + ' ' + <varCol2>

FETCH NEXT FROM <CursorName> INTO <varCol1> , <varCol2>

END

CLOSE <CursorName>
DEALLOCATE <CursorName>

DECLARE CurrMobComp CURSOR FOR

SELECT ModelNo , Company
FROM MobileStoreInventory

DECLARE @ModelNo VARCHAR(20),

@Company VARCHAR(20);

OPEN CurrMobComp
FETCH NEXT FROM CurrMobComp INTO @ModelNo , @Company

WHILE @@FETCH_STATUS = 0
BEGIN
print @ModelNo + ' ' + @Company

FETCH NEXT FROM CurrMobComp INTO @ModelNo , @Company

END

CLOSE CurrMobComp
DEALLOCATE CurrMobComp
TRIGGER – DELETE with Cursor

----TRIGGER – DELETE with Cursor

CREATE TRIGGER TrMobStoreDelCur
ON MobileStoreInventory
FOR DELETE
AS
BEGIN
DECLARE @ModelNo VARCHAR(20);

DECLARE MobCursor CURSOR FOR

SELECT ModelNo
FROM Deleted

OPEN MobCursor
FETCH NEXT FROM MobCursor INTO @ModelNo

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'HERE!'
print @ModelNo

DECLARE @log VARCHAR(500)

SET @log = 'Record related to model no ' + @ModelNo + ' has been
deleted.'
INSERT INTO LogFile (TimeStamp,ErrorMsg)
VALUES (GETDATE(), @log)

FETCH NEXT FROM MobCursor INTO @ModelNo

END

CLOSE MobCursor
DEALLOCATE MobCursor
END
TRIGGER – UPDATE with Cursor

----TRIGGER – UPDATE with Cursor

CREATE TRIGGER TrMobStoreUpd
ON MobileStoreInventory
FOR UPDATE
AS
BEGIN
DECLARE @ModelNo VARCHAR(20);

DECLARE MobCursor CURSOR FOR

SELECT ModelNo
FROM Inserted

OPEN MobCursor
FETCH NEXT FROM MobCursor INTO @ModelNo

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'HERE!'
print @ModelNo

DECLARE @log VARCHAR(500)

SET @log = 'Record related to model no ' + @ModelNo + ' has been
updated.'
INSERT INTO LogFile (TimeStamp,ErrorMsg)
VALUES (GETDATE(), @log)

FETCH NEXT FROM MobCursor INTO @ModelNo

END

CLOSE MobCursor
DEALLOCATE MobCursor
END
STORED PROCEDURE

ALTER PROCEDURE testProc_sp

AS
BEGIN
SET NOCOUNT ON;

DECLARE CurPickData CURSOR FOR

SELECT EMPID , ENAME
FROM EMP

DECLARE @EMPID INT,

@ENAME VARCHAR(50);

OPEN CurPickData
FETCH NEXT FROM CurPickData INTO @EMPID, @ENAME

WHILE @@FETCH_STATUS = 0
BEGIN
print 'The EmpID is '+ cast(@EMPID as VARCHAR) + ' and the name is
' + @ENAME

FETCH NEXT FROM CurPickData INTO @EMPID , @ENAME

END

CLOSE CurPickData
DEALLOCATE CurPickData

END
GO

EXEC testProc_sp
ALTER PROCEDURE spUpdSal
@EMPID INT , @INC DECIMAL(7,2)
AS
BEGIN

DECLARE @Sal DECIMAL(7,2);

SELECT @Sal = Salary from EMP WHERE EMPID = @EMPID

UPDATE EMP
SET Salary = @Sal + @INC
WHERE EMPID = @EMPID
END

CREATE PROCEDURE spTestCurr

AS
BEGIN

DECLARE CurPickData CURSOR FOR

SELECT EMPID , ENAME
FROM EMP

DECLARE @EMPID INT,

@ENAME VARCHAR(50);

OPEN CurPickData
FETCH NEXT FROM CurPickData INTO @EMPID, @ENAME

WHILE @@FETCH_STATUS = 0
BEGIN
print 'The EmpID is '+ cast(@EMPID as VARCHAR) + ' and the name is '
+ @ENAME

EXEC spUpdSal @EMPID = @EMPID , @INC = 500

SELECT EMPID , SALARY FROM EMP WHERE EMPID = @EMPID

FETCH NEXT FROM CurPickData INTO @EMPID , @ENAME

END

CLOSE CurPickData
DEALLOCATE CurPickData

END

EXEC spTestCurr