SQL Server/T-SQL/Data Type/int

Материал из SQL эксперт
Перейти к: навигация, поиск

int type column

   <source lang="sql">

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

</source>
   
  


int type parameter

   <source lang="sql">

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>

</source>
   
  


RETURNS INT from a function

   <source lang="sql">

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

</source>