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
