create function ganesha(@id int)
returns int
as
begin
declare @work int
select @work=avg(salary) from emp_info where id=@id;
return @work;
end;
declare @my int
select dbo.ganesha(1)
b)
alter function myfun(@date datetime)
returns varchar(50)
as begin
declare @myoutput varchar(10)
set @myoutput=convert(varchar(10),@date,101)
return @myoutput
end;
select dbo.myfun(getdate())
<strong>Inline Table</strong><strong> User Defined</strong><strong> Function</strong>
<strong> </strong>
create function getname(@firstletter char(1))
returns table
as
return select *from emp_info where left(emp_name,1)=@firstletter;
select *from dbo.getname('W')
<strong>Multi Statement</strong><strong> User defined</strong><strong> function</strong>
CREATE FUNCTION dbo.multi_test(@FirstLetter char1))
RETURNS @Result TABLE
(
fname varchar(20),
worka datetime,
workcomp varchar(16)
)
AS
BEGIN
INSERT INTO @Result
(fname,worka)
SELECT emp_name, worka
FROM emp_info
WHERE LEFT(emp_name, 1) = @FirstLetter
UPDATE @Result
SET workcomp = 'N'
UPDATE @Result
SET workcomp= 'Y'
WHERE worka < '01/01/1991'
RETURN
END
select *from dbo.multi_test('W')
