SQL Server 2005 Samples (1)

SQL Operatins

/* 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
	qq
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