Understanding Table Valued Functions in SQL

--Prepare Sample Data to work upon
CREATE TABLE MST_MyUsers
(
Id int IDENTITY(1,1)
,Name nvarchar(100)
,CreatedDate datetime
)
INSERT INTO MST_MyUsers VALUES ('Tony','2011-12-01')
INSERT INTO MST_MyUsers VALUES ('Sam','2012-04-01')
INSERT INTO MST_MyUsers VALUES ('James','2012-07-01')
GO
--Gets list of users created till specified date
CREATE FUNCTION [dbo].[udf_GetUserData]
(
@CreatedDate datetime
)
RETURNS
@UserDetails TABLE
(
Id int,
Name nvarchar(50),
CreatedDate datetime
)
AS
BEGIN
--Returning sample data
--In practical scenarios, we will have this data coming from a existing table or from logical computation
INSERT INTO @UserDetails
SELECT Id,Name,CreatedDate
FROM MST_MyUsers
WHERE CreatedDate <= @CreatedDate
RETURN
END
GO
------------------------------------------------------------
--Select users created upto 01 Jan 2011
SELECT * FROM udf_GetUserData('2011-01-01')
--Select users created upto 01 Jan 2012
SELECT * FROM udf_GetUserData('2012-01-01')
--Select users created upto 01 Jan 2013
SELECT * FROM udf_GetUserData('2013-01-01')
------------------------------------------------------------
--Drop function created to get user details
DROP FUNCTION [udf_GetUserData]
--Delete Sample data
DROP TABLE MST_MyUsers