Function sql server – New Zone+ Application

create function fnComputeAge(@DOB datetime)
returns nvarchar(50)
as begin
	declare @tempdate datetime, @years int, @months int, @days int
		select @tempdate = @DOB

		select @years = datediff(year, @tempdate, getdate()) - case when (month(@DOB) &gt; month(GETDATE())) or (MONTH(@DOB)
		= month (getdate()) and day(@DOB) &gt; DAY(getdate())) then 1 else 0 end
		select @tempdate = dateadd(year, @years, @tempdate)

		select @months = datediff(month, @tempdate, getdate()) - case when day(@DOB) &gt; day(getdate()) then 1 else 0 end
		select @tempdate = dateadd(MONTH, @months, @tempdate)

		select @days = datediff(day, @tempdate, getdate())

	declare @Age nvarchar(50)
		set @Age = cast(@years as nvarchar(4)) + ' Years ' + cast(@months as nvarchar(2)) + 
		' Months ' + cast(@days as nvarchar(2)) + ' Days old'
	return @Age
end

--- saame vaadata kasutajate vanust
select Id, Name, DateOfBirth, dbo.fnComputeAge(DateOfBirth) 
as Age from EmployeesWithDates

create function dbo.CalculateAge(@DOB date)
returns int
as begin
declare @Age int

set @Age = DATEDIFF(YEAR, @DOB, GETDATE()) -
	case
		when (MONTH(@DOB) &gt; MONTH(getdate())) or
			 (MONTH(@DOB) &gt; MONTH(GETDATE()) and DAY(@DOB) &gt; day(GETDATE()))
		then 1
		else 0
		end
	return @Age
end

create function fn_EmployeesByGender(@Gender nvarchar(10))
returns table
as
return (select Id, Name, DateOfBirth, DepartmentId, Gender
		from EmployeesWithDates
		where Gender = @Gender)

CREATE procedure nimi ….	CREATE function nimi return ….
teeb mitu tegevust korraga	tagastab väärtused
-kutse EXEC nimi; EXECUTE nimi;	-kutse select veerunimi, funktsiooninimi FROM tabelinimi
INPUT, OUTPUT	INPUT
Protseduur = teeb tegevusi (muudab andmeid, käivitab mitmeid käske, ei pea väärtust tagastama)	Funktsioon = arvutab ja tagastab midagi (väärtuse või tabeli; saab kasutada SELECT-is).