SQL Server/T-SQL/Data Type/int

int type column

 

6> CREATE TABLE T (
7>     int1 int,
8>     bit1 bit,
9>     varchar1 varchar(3),
10>     dec1 dec(5,2),
11>     cmp1 AS (int1 + bit1)
12> )
13> GO
1>
2> INSERT T (int1, bit1) VALUES (1, 0)
3> INSERT T (int1, varchar1) VALUES (2, "abc")
4> INSERT T (int1, dec1) VALUES (3, 5.25)
5> INSERT T (bit1, dec1) VALUES (1, 9.75)
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> drop table t;
3> GO

int type parameter

 
2>
3> CREATE TABLE Employees (
4>      EmployeeID int NOT NULL ,
5>      LastName nvarchar (20) NOT NULL ,
6>      FirstName nvarchar (10) NOT NULL ,
7>      Title nvarchar (30) NULL ,
8>      TitleOfCourtesy nvarchar (25) NULL ,
9>      BirthDate datetime NULL ,
10>     HireDate datetime NULL ,
11>     Address nvarchar (60) NULL ,
12>     City nvarchar (15) NULL ,
13>     Region nvarchar (15) NULL ,
14>     PostalCode nvarchar (10) NULL ,
15>     Country nvarchar (15) NULL ,
16>     HomePhone nvarchar (24) NULL ,
17>     Extension nvarchar (4) NULL ,
18>     Photo image NULL ,
19>     Notes ntext NULL ,
20>     ReportsTo int NULL ,
21>     PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3>
4> CREATE TABLE RegionPlace
5>      (RegionID nvarchar (20) NOT NULL ,
6>      TerritoryDescription nchar (50) NOT NULL ,
7>         RegionID int NOT NULL
8> )
9> GO
1>
2> Insert Into RegionPlace Values ("01581","Westboro",1)
3> Insert Into RegionPlace Values ("01730","Bedford",1)
4> Insert Into RegionPlace Values ("01833","Georgetow",1)
5> Insert Into RegionPlace Values ("95060","Santa Cruz",2)
6> Insert Into RegionPlace Values ("98004","Bellevue",2)
7> Insert Into RegionPlace Values ("98052","Redmond",2)
8> Insert Into RegionPlace Values ("98104","Seattle",2)
9> Go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE EmployeeRegion
2>      (EmployeeID int NOT NULL,
3>       RegionID nvarchar (20) NOT NULL
4> )
5> GO
1>
2> Insert Into EmployeeRegion Values (1,"06897")
3> Insert Into EmployeeRegion Values (1,"19713")
4> Insert Into EmployeeRegion Values (9,"48084")
5> Insert Into EmployeeRegion Values (9,"48304")
6> Insert Into EmployeeRegion Values (9,"55113")
7> Insert Into EmployeeRegion Values (9,"55439")
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE FUNCTION dbo.udf_EmpRegionPlaceTAB (
3>     @EmployeeID int
4> )   RETURNS TABLE
5> AS RETURN
6> SELECT TOP 100 PERCENT WITH TIES
7>        et.RegionID
8>      , t.TerritoryDescription as [Territory]
9>      , t.RegionID
10>     FROM EmployeeRegion et
11>          LEFT OUTER JOIN RegionPlace t
12>              ON et.RegionID = t.RegionID
13>     WHERE et.EmployeeID = @EmployeeID
14>     ORDER BY t.TerritoryDescription
15>
16> GO
1>
2> DECLARE @EmpID int
3> SELECT @EmpID = EmployeeID
4>     FROM Employees
5>     WHERE FirstName = "Andrew" and LastName = "Fuller"
6>
7> SELECT * FROM udf_EmpRegionPlaceTAB(@EmpID)
8> GO
RegionID          Territory                                          RegionID
-------------------- -------------------------------------------------- -----------
(0 rows affected)
1>
2>
3>
4> drop FUNCTION dbo.udf_EmpRegionPlaceTAB;
5> GO
1> drop table EmployeeRegion;
2> drop table RegionPlace;
3> drop table Employees;
4> GO
1>

RETURNS INT from a function

 

2>
3>
4> CREATE TABLE EmployeeRegion
5>      (EmployeeID int NOT NULL,
6>       RegionID nvarchar (20) NOT NULL
7> )
8> GO
1>
2> Insert Into EmployeeRegion Values (1,"06897")
3> Insert Into EmployeeRegion Values (1,"19713")
4> Insert Into EmployeeRegion Values (9,"48084")
5> Insert Into EmployeeRegion Values (9,"48304")
6> Insert Into EmployeeRegion Values (9,"55113")
7> Insert Into EmployeeRegion Values (9,"55439")
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Employees (
4>      EmployeeID int NOT NULL ,
5>      LastName nvarchar (20) NOT NULL ,
6>      FirstName nvarchar (10) NOT NULL ,
7>      Title nvarchar (30) NULL ,
8>      TitleOfCourtesy nvarchar (25) NULL ,
9>      BirthDate datetime NULL ,
10>     HireDate datetime NULL ,
11>     Address nvarchar (60) NULL ,
12>     City nvarchar (15) NULL ,
13>     Region nvarchar (15) NULL ,
14>     PostalCode nvarchar (10) NULL ,
15>     Country nvarchar (15) NULL ,
16>     HomePhone nvarchar (24) NULL ,
17>     Extension nvarchar (4) NULL ,
18>     Photo image NULL ,
19>     Notes ntext NULL ,
20>     ReportsTo int NULL ,
21>     PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3> CREATE FUNCTION dbo.udf_EmpTerritoryCOUNT (
4>     @EmployeeID int
5> )   RETURNS INT
6> AS BEGIN
7>     DECLARE @RegionPlace int
8>     SELECT @RegionPlace = count(*)
9>         FROM EmployeeRegion
10>         WHERE EmployeeID = @EmployeeID
11>
12>     RETURN @RegionPlace
13> END
14> GO
1>
2>
3> -- Get the 3 employees with the most RegionPlace
4> SELECT TOP 3 LastName, FirstName
5> , dbo.udf_EmpTerritoryCOUNT(EmployeeID) as RegionPlace
6>     FROM Employees
7>     WHERE dbo.udf_EmpTerritoryCOUNT(EmployeeID) > 3
8>     ORDER BY dbo.udf_EmpTerritoryCOUNT(EmployeeID) desc
9> GO
LastName             FirstName  RegionPlace
-------------------- ---------- -----------
(0 rows affected)
1>
2>
3> drop FUNCTION dbo.udf_EmpTerritoryCOUNT;
4> GO
1>
2>
3> drop table EmployeeRegion;
4> GO
1>
2> drop table Employees;
3> GO