/* Example for PRINT */
PRINT 'sample'
Go
/* Example for SELECT */
SELECT 'book', 200;
GO
SELECT 'book' as material, 200 as items;
GO
Variables and Data Types
/*Example for Declare, initialize, display value of a Variable */
DECLARE @varText INT;
SET @varText = 10;
SELECT @varText as varText;
GO
/*Boolean Variable. you can use the BIT or bit keyword.*/
/* initialize 0 -> false or Any number -> true */
DECLARE @varText BIT;
set @varText = 0;
SELECT @varText as varText;
GO
/* Decimal variable */
/* (either decimal or numeric would produce the same effect in SQL Server */
DECLARE @Distance DECIMAL;
SET @Distance = 27.99;
PRINT @Distance;
GO
/* Floating point */
DECLARE @Distance FLOAT;
SET @Distance = 648.16;
PRINT @Distance;
GO
/* Money Variable */
DECLARE @YearlyIncome Money;
SET @YearlyIncome = 48500.15;
SELECT @YearlyIncome AS [Yearly Income];
GO
/* DATETIME Variable */
DECLARE @IndependenceDay DATETIME;
SET @IndependenceDay = '01/01/1960';
SELECT @IndependenceDay AS [Independence Day];
GO
/*Time*/
DECLARE @ArrivalTime datetime;
SET @ArrivalTime = '18:22';
SELECT @ArrivalTime as [Arrival Time];
GO
/*CHAR Variable*/
DECLARE @Gender CHAR, @LeftMostChar CHAR;
SET @GENDER = 'M';
SET @LeftMostChar = 'Male';
SELECT @Gender as Gender, @LeftMostChar as LeftMostChar;
GO
SQL Expressions
/*IF Condition Statement*/
DECLARE @bool BIT;
SET @bool = 0;
IF @bool <> 0
PRINT 'bool value is True';
ELSE
PRINT 'bool value is False';
GO
/*CASE...WHEN...THEN */
DECLARE @CharGender Char(1), @Gender Varchar(20);
SET @CharGender = 'M';
SET @Gender =
CASE @CharGender
WHEN 'm' THEN 'Male'
WHEN 'M' THEN 'Male'
WHEN 'f' THEN 'Female'
WHEN 'F' THEN 'Female'
ELSE 'Unknown'
END;
SELECT @Gender as Gender
GO
/*WHILE Condition Statement*/
DECLARE @Number As int;
SET @Number = 3;
WHILE @Number < style="color: rgb(51, 51, 255);">BEGIN
SELECT @Number as Number;
SET @Number = @Number + 1;
END;
GO
Functions
/* Create Function */
CREATE FUNCTION addition(@a INT, @b INT)
RETURNS INT
AS
BEGIN
DECLARE @result INT;
SET @result = @a + @b;
RETURN @result;
END;
GO
/* Call Function ------*/
DECLARE @Nbr1 INT,
@Nbr2 INT
SET @Nbr1 = 4268
SET @Nbr2 = 26
SELECT @Nbr1 as First,
@Nbr2 as Second,
user.addition(@Nbr1, @Nbr2) as Result;
GO
Built-in Functions
/* Casting a Value */
DECLARE @stringV VARCHAR(10), @intV INT
SET @stringV = '10';
SET @intV = CAST(@stringV as INT);
SELECT @intV as Result
GO
/* Converting a Value */
/* Casting a Value */
DECLARE @stringV VARCHAR(10), @intV INT, @result INT
SET @stringV = '6';
SET @intV = CAST(@stringV as INT);
SET @result = @intV + @intV;
PRINT 'Result :' + CONVERT(VARCHAR(10), @result, 10);
GO
/* The Length of a String */
DECLARE @stringV varchar(10), @result INT
SET @stringV = 'ABCDEF';
SET @result = LEN(@stringV);
PRINT 'Result :' + CONVERT(VARCHAR(10), @result, 10);
GO
/* Date and Time Based Functions*/
Type of Value | Abbreviation | As a result |
Year | yy | The function will return the number of years that have elapsed between the start and the end dates |
yyyy | ||
quarter | q | The function will return the number of quarters of a year that have elapsed between the start and the end dates |
Month | m | The function will return the number of months that have elapsed between the start and the end dates |
mm | ||
dayofyear | y | The function will return the number of days of a year that have elapsed between the start and the end dates |
dy | ||
Day | d | The function will return the number of days that have elapsed between the start and the end dates |
dd | ||
Week | wk | The function will return the number of weeks that have elapsed between the start and the end dates |
ww | ||
Hour | hh | The function will return the number of hours that have elapsed between the start and the end times or dates |
minute | n | The function will return the number of minutes that have elapsed between the start and the end times or dates |
mi | ||
second | s | The function will return the number of seconds that have elapsed between the start and the end times or dates |
ss | ||
millisecond | ms | The function will return the number of milliseconds that have elapsed between the start and the end times or dates |
/* The Current System Date and/or Time */
DECLARE @currentDateTime DATETIME
SET @currentDateTime = GETDATE();
SELECT @currentDateTime AS CurrentDateTime;
GO
/*Date/Time Addition
Format :- DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)*/
DECLARE @Anniversary As DateTime;
SET @Anniversary = '2002/10/02';
SELECT DATEADD(yy, 4, @Anniversary) AS Anniversary;
GO
/*Date/Time Addition
Format :- DATEDIFF(TypeOfValue, StartDate, EndDate) */
DECLARE @DateHired As DateTime, @CurrentDate As DateTime;
SET @DateHired = '2006/10/12';
SET @CurrentDate = GETDATE();
SELECT DATEDIFF(mm, @DateHired, @CurrentDate)
AS [Current Experience], DATEDIFF(dd, @DateHired, @CurrentDate) as Days;
GO
No comments:
Post a Comment