How to Write and Call Scaler Function in SQL Server 2008

 At First Create A Scaler Function
Click '+' Sign near the Database in the Object Explorer
Click '+'  Programmability
Click '+'  Functions
Right Click on 'Scaler-Valued Functions'
New Scaler-Valued Functions

Now Write the Following Function
Create FUNCTION fncNEwID (@Param Varchar(15))
RETURNS int
AS
BEGIN
Declare @Retval int
Select @Retval=0,@Param=Upper(@Param)

if(@Param ='CATEGORY')
Begin
Select @Retval=Isnull(Max(CategoryId),0)+1
from Category
End
Return @Retval
END
GO

Now Call The Function
DECLARE @MyDecimalVar int
EXEC @MyDecimalVar = dbo.fncNEwID 'CUSTOMER'
SELECT @MyDecimalVar
GO