Functions in SQL Server 2008

USE chitharans

SELECT *FROM EmployeeOfficial
SELECT *FROM EmployeePersonal
-------------Scalar value Function (return single value)----------
CREATE  FUNCTION Employeenumbers (@empno int)
RETURNS int
AS
BEGIN
DECLARE @returnvalue int
SELECT @returnvalue=Emp_No FROM EmployeeOfficial WHERE Emp_No=@empno
RETURN @returnvalue
END

----------

------Execution-----

SELECT dbo.Employeenumbers(101)
SELECT  1 AS number

-------------Inline Functions (return a table)------------

CREATE  FUNCTION Employeefunction (@empno int)
RETURNS TABLE
AS
RETURN SELECT *FROM EmployeeOfficial WHERE Emp_No=@empno


--------------Execution---------
SELECT *FROM EmployeeFunction(101)


-------------Table valued Funtions(Multiple operation, complex logic just like SP)---

CREATE  FUNCTION Employeetablevaluefunction12 (@empno int)
RETURNS @result TABLE(name VARCHAR(20))
AS
BEGIN
INSERT INTO @result(name)
SELECT [Emp_Name] FROM EmployeeOfficial WHERE Emp_No=100
UPDATE @result SET name ='ChitharanPraveen'
RETURN
END

---Execution-------
SELECT *FROM Employeetablevaluefunction1(100)
SELECT *FROM EmployeeOfficial