SP、VIEW、FUNC 範本

Stored Procedure	Function	View
--================================= -- 使用 RETURN 回傳一個純量值 --================================= CREATE PROCEDURE uspGetProductPrice @ProductID as int AS [BEGIN] DECLARE @Price money; SELECT @Price=Price FROM Product WHERE ProductID= @ProductID RETURN @Price; --必要 [END] declare @Price1 int exec @Price1 = uspGetProductPrice 2 select @Price1	--================================= -- 回傳純量值 --================================= CREATE FUNCTION funGetProductPrice ( @ProductID as int ) RETURNS money [AS] BEGIN DECLARE @Price money; SELECT @Price=Price FROM Products WHERE ProductID= @ProductID RETURN @Price END select dbo.funGetProductPrice (4) declare @Price1 int set @Price1 = dbo.funGetProductPrice (4) select @Price1
--================================= -- 使用 OUTPUT 參數回傳 --================================= CREATE PROCEDURE uspGetProductPrice @ProductID as int, @Price int OUTPUT AS [BEGIN] SELECT @Price=Price FROM Product WHERE ProductID= @ProductID RETURN --可省略 [END] declare @Price int; exec uspGetProductPrice 2, @Price OUTPUT select @Price	--================================= -- 回傳資料表 (使用multi-statement) --================================= CREATE FUNCTION funGetProduct_M ( @ProductID as int ) RETURNS @TmpTable TABLE( ProductID int, ProductName nvarchar(255), Price int ) [AS] BEGIN INSERT @TmpTable SELECT ProductID, ProductName, Price FROM Product WHERE ProductID= @ProductID RETURN --必要 END select * from dbo.funGetProduct_M(4)
--================================= -- 使用 SELECT 回傳一個資料表 --================================= CREATE PROCEDURE uspGetProduct @ProductID int AS [BEGIN] SELECT ProductID, ProductName, Price FROM Products WHERE ProductID= @ProductID RETURN; --可省略 [END] exec uspGetProduct 4	--================================= -- 回傳資料表 --================================= CREATE FUNCTION funGetProduct_S ( @ProductID as int ) RETURNS TABLE [AS] RETURN ( SELECT ProductID, ProductName, Price FROM Products WHERE ProductID= @ProductID ) select * from dbo.funGetProduct_S(4)	CREATE VIEW dbo.vwCheapProduct AS SELECT ProductID,ProductName,Price FROM dbo.Products WHERE Price < 10 select * from vwCheapProduct