/ *****************************************
At — Geovin Du Tu Tsui Man association Friends of Computer Information Technology Co., Ltd.
— Group of Companies relatives of employees Referrals name. A table of two foreign keys from the same table
***************************************** /
– Employee table
IF EXISTS (select * from sysobjects where id = object_id (N ‘[dbo]. [Employeetest]’) and OBJECTPROPERTY (id, N’IsUserTable ‘) = 1)
The DROP TABLE [Employeetest,]
GO
create table Employeetest is
(
Eid INT IDENTITY (1,1) PRIMARY KEY,
Ename nvarchar (30) not null,
)
– Insert data
insert into Employeetest (Ename) values ??(‘geovindu’)
insert into Employeetest (Ename) values ??(‘geovindu1’)
insert into Employeetest (Ename) values ??(‘geovindu2’)
insert into Employeetest (Ename) values ??(‘geovindu3’)
insert into Employeetest (Ename) values ??(‘geovindu4’)
insert into Employeetest (Ename) values ??(‘geovindu5’)
— Internal Referrals table
IF EXISTS (select * from sysobjects where id = object_id (N ‘[dbo]. [Retationtest]’) and OBJECTPROPERTY (id, N’IsUserTable ‘) = 1)
The DROP TABLE [Retationtest,]
GO
create table Retationtest is
(
rid INT IDENTITY (1,1) PRIMARY KEY,
REkey int not null, – employees (Referrals) foreign key
REpkey int not null, – employees (employees) foreign key
ReationsName nvarchar (30) not null
)
– Insert
insert into Retationtest (REkey, REpkey, ReationsName) values ??(1,3, ‘sz’)
– Function
if exists (select * from dbo.sysobjects where id = object_id (N ‘[dbo]. [GetName]’) and xtype in (N’FN ‘, N’IF’, N’TF ‘))
drop function [dbo]. [GetName]
CREATE FUNCTION GetName
(
@ REpkey int – parameter
)
returns varchar (500)
as
begin
DECLARE @ ename nvarchar (50)
select _AT_ ename = Ename from Employeetest where
return @ ename
end
GO
– Test
declare @ ename nvarchar (50), @ REpkey int
set _AT_ REpkey = 2
select _AT_ ename = Ename from Employeetest where
select _AT_ ename
– View
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ‘View_Employeetest’)
DROP VIEW View_Employeetest is
GO
create view View_Employeetest is
as
Select RID AS ‘ID’, the Ename AS ‘Referrals name’, dbo.GetName (REpkey) AS ’employees name’ ReationsName AS ‘relations’ from Employeetest Retationtest
where Employeetest.Eid = Retationtest.REkey
– And Employeetest.Eid = Retationtest.REpkey
GO
– Show
select * from View_Employeetest
