Functions In Sqlserver

Table valued Functions

create function [dbo].[GetInformation](@ParameterId int)
returns table as
return (
select count(*) as total from tablename where condition=@ParameterId)

to view results
select * from GetInformation(10) where 10 is parent table's primary key Id

Scalar valued Functions

ALTER FUNCTION [dbo].[GenerateReceiptNumber](@FirstName varchar(50),@Year varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Result varchar(50)
Declare @FN varchar(50)
Declare @YR varchar(50)

set @FN = @FirstName
set @YR = @Year


SELECT @Result = upper(@FN + @YR)

RETURN @Result

END

to view results write

select dbo.GenerateReferenceNumber('MyName','2009')
OutOut -- > MyName2009