Tutorial SQLServer 05

James Knight
242
Constant
SQL
DECLARE
DECLARE Options
Transact-
DECLARE
SQL
C/C++, Pascal, Java, C#
Transact-SQL
DECLARE @264
@Second @act @_n

_

_
@act_52t @_24


@DateHired, @_RealSport, @DriversLicenseNumber
DECLARE @VariableName DataType;
DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n
DataType_n;
C/C++, C#, Java, Pascal
Initializing a Variable
Null
SET
SELECT
SELECT @VariableName = DesiredValue
SET @VariableName = DesiredValue
PRINT
SELECT
SQL
10
bit
DECLARE @IsOrganDonor bit;
0
0
False
True
1
DECLARE @IsMarried bit
SET @IsMarried = 1
SELECT @IsMarried AS [Is Married?];
GO
2
Integer
2,147,483,648
Integer
2,147,483,64
79,435,794
SQL
int
Integer
DECLARE @Category int;
SET @Category = 1450;
PRINT @Category;
GO
1450
4 bytes
tinyint
int
tinyint
255
0
int
smallint
32.768
32.767
DECLARE @NumberOfPages SMALLINT;
SET @NumberOfPages = 16;
SELECT @NumberOfPages AS [Number of Pages];
GO
int
9.223.372.036.854.775.807
bigint
9.223.372.036.854.775.808
DECLARE @CountryPopulation BigInt;
SET @CountryPopulation = 16500000;
SELECT @CountryPopulation AS 'Country Population';
GO
binary data
0x7238, 0xFA36
varbinary
hexadecimal
varbinary
Integer
1
DECLARE @IsMarried bit, @EmplStatus int;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SELECT @IsMarried AS [Is Married?],
@EmplStatus AS [Employment Status];
GO
2
F5
Decimal
44.80
12.625
Control Panel
decimal
numeric
DECLARE @Distance DECIMAL;
SET @Distance = 648.16;
PRINT @Distance;
GO
648
648.16
648.16
DECLARE @Distance decimal(5,2);
SET @Distance = 648.12
SELECT @Distance;
GO
5
648.12
floating-point
Float
decimal
DECLARE @Radius FLOAT;
SET @Radius = 48.16;
SELECT @Radius AS Radius;
GO
42005
3
int
real
decimal
226
float
Microsoft SQL Server
Microsoft SQL Server
28 38
SQL Server 7
1450.4227
5
numeric
1
12.05
smallmoney
money
decimal numeric
18
0
1
DECLARE @IsMarried bit,
@EmplStatus int,
@WeeklyHours Decimal(6,2);
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SELECT @IsMarried AS [Is Married?],
@EmplStatus AS [Employment Status],
@WeeklyHours AS Hours;
GO
2
money
922،337،203،685،477.5808
money
922،337،203،685،477.5807
DECLARE @YearlyIncome Money;
SET @YearlyIncome = 48500.15;
SELECT @YearlyIncome AS [Yearly Income];
GO
money
214،748.3647
4
Microsoft SQL Server
smallmoney
214،748.3648
smallmoney money
1
DECLARE @EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
2
DATETIME
Microsoft SQL Server
datetime
31
1753
1
9999
DATETIME
Date Separator
Control Panel
DECLARE @IndependenceDay DATETIME;
SET @IndependenceDay = '01/01/1960';
SELECT @IndependenceDay AS [Independence Day];
GO
Control Panel
DECLARE @ArrivalTime datetime;
SET @ArrivalTime = '18:22';
SELECT @ArrivalTime AS [Arrival Time];
GO
datetime
1900
smalldatetime
1
2079
6
1
DECLARE @DateHired DateTime,
@EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @DateHired = '12/05/1998';
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @DateHired AS [Date Hired],
@EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
2
char
DECLARE @Gender char;
char
DECLARE @Gender char;
SET @GENDER = 'M';
SELECT @Gender AS Gender;
GO
DECLARE @Gender char;
SET @GENDER = 'Male';
SELECT @Gender AS Gender;
GO
M
String
varchar
8 kilobytes
varchar
varchar
""
SQLCMD.EXE
char
ASCII
text
kilobytes 8
varchar
text
ntext
varchar
nvarchar nchar
text
char
Unicode
1
DECLARE @FirstName varchar(20),
@LastName varchar(20),
@FullName varchar(40),
@DateHired DateTime,
@EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @FirstName
= 'Samuel';
SET @LastName
= 'Weinberg';
SET @FullName
= @LastName + ', ' +@FirstName;
SET @DateHired
= '12/05/1998';
SET @IsMarried
= 1;
SET @EmplStatus
= 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
@DateHired AS [Date Hired],
@EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
2
My Documents
Variables

40

2

UtilityCompany1
8.50

BaseCharge
6.50
First700
8.50
224
First700


