SQL函數和存儲過程模板示例
學習SQL數據庫，函數和存儲過程都是非常重要的，下面就將為您示例SQL函數和存儲過程模板，供您參考，希望對您學習SQL函數和存儲過程能有所啟迪。
--標量值函數
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN#p#
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
END
GO
--////////////////////////////////////////////////////////////////////////////////////////////////////////////
--內聯表值函數
-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE #p#
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////
--多語句表值函數
-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS #p#
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column definitions for the TABLE variable here
<Column_1, sysname, c1> <Data_Type_For_Column1, , int>,
<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
RETURN
END
GO
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////
--多語句表值函數
DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DuplicateId Int
SELECT @MergeDate = GetDate()
DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MergeDuplicateCustomers @MasterId, @DuplicateId
UPDATE DuplicateCustomers
SET
IsMerged = 1,
MergeDate = @MergeDate
WHERE
MasterCustomerId = @MasterId AND
DuplicateCustomerId = @DuplicateId
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
【編輯推薦】
