CREATE SCALAR VALUE FUNCTION IN SQL EXAMPLE

TBL_DEPARTMENT_MAST TABLE

 

Dept_Id	Dept_Code	Dept_Name	Dept_Status
41	IES	INSTRUMENT/ELE/SYSTEM	A
42	CVL	CIVIL	A
43	REF	REFRIGERATION	A
54	PIO	PILOT PLANT	A
55	MKTG	MARKETING	A
59	F&S	FIRE & SAFETY	A

CREATE FUNCTION [dbo].[DeptName]

(

@DeptId numeric(18,0)

)

RETURNS varchar(56)/* datatype */

AS

BEGIN

Declare @DeptName varchar(56);

/* sql statement ... */

SELECT @DeptName=TBL_DEPARTMENT_MAST.Dept_Name

FROM TBL_DEPARTMENT_MAST

WHERE (TBL_DEPARTMENT_MAST.Dept_Id = @DeptId)

Return @DeptName

END