SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER             FUNCTION [dbo].[GrafUsView](@Dat Datetime)
RETURNS
@TAB Table (ID INT IDENTITY (1, 1) NOT NULL,SotrID int,DolgID int,NameSotr varchar(100),NameDolgn varchar(100))

AS
BEGIN

Insert @tab (SotrID,DolgID,NameSotr,NameDolgn)
SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr1 left join dolgnost D1 On D1.Id = G.SD1
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr2 left join dolgnost D1 On D1.Id = G.SD2
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr3 left join dolgnost D1 On D1.Id = G.SD3
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr4 left join dolgnost D1 On D1.Id = G.SD4
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr5 left join dolgnost D1 On D1.Id = G.SD5
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr6 left join dolgnost D1 On D1.Id = G.SD6
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr7 left join dolgnost D1 On D1.Id = G.SD7
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr8 left join dolgnost D1 On D1.Id = G.SD8
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr9 left join dolgnost D1 On D1.Id = G.SD9
where data = @dat

Union all

SELECT S1.ID,D1.ID,S1.Firstname S1, D1.name SD1 FROM [graf] G
  left join sotr S1 On S1.Id=G.Sotr10 left join dolgnost D1 On D1.Id = G.SD10
where data = @dat

delete from @tab where sotrid is null
delete from @tab where dolgid in (select id from dolgnost where zapis=0)
Update @tab set namesotr = namesotr+char(10)+char(13)+namedolgn

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO