* Microsoft SQL Server 2008 R2
Problem :
* Want to using CREATE FUNCTION with Parameters syntax then Return an TABLE
Solution :
1. Example Table,
2. Type the code below,
---------------------------------------------------------------------
---------------------------------------------------------------------
-----CODE START
CREATE FUNCTION [dbo].[A_Orders_By_Month_1]
(
@AMonth varchar(10)
)
RETURNS @_AN_Orders_By_Month_1 TABLE
(
OrderID int,
CustomerID varchar(20),
OrderDate datetime,
ShipName varchar(200)
)
AS
BEGIN
insert into @_AN_Orders_By_Month_1
select OrderID, CustomerID, OrderDate, ShipName
from Orders
where month(OrderDate)=@AMonth
RETURN
END
-----CODE END
---------------------------------------------------------------------
---------------------------------------------------------------------
3. Try to run the Function,
---------------------------------------------------------------------
---------------------------------------------------------------------
-----CODE START
select * from [A_Orders_By_Month_1] ( '12' )
-----CODE END
---------------------------------------------------------------------
---------------------------------------------------------------------
4. Optional, To remove the Function,
---------------------------------------------------------------------
---------------------------------------------------------------------
-----CODE START
drop function [A_Orders_By_Month_1]
-----CODE END
---------------------------------------------------------------------
---------------------------------------------------------------------
About :
01 - Create Function with Return Table in SQL Server
02 - Syntax : CREATE FUNCTION - Return Table - With Parameters
03 - Example of Create Function in SQL with Returns Value
04 - Example of Create Function (No Parameter) in SQL with Returns Value
05 - How to Use Function Intersect
06 - How to Use Function Except