SQL Server Functions, stored procedures, cursors, and transaction templates

1. scalar function: the result is a single value that can contain a logical processing process. Uncertain System functions such as getdate () cannot be used.
Copy codeThe Code is as follows:
-- Scalar Value Function
-- ===================================================== ==========
-- 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 supported 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
-- 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

2. inline Table value function: the return value is a table, which is implemented by only one SQL statement without the logic processing capability. Query of large data volumes can be executed.

Copy codeThe Code is as follows:
-- Inline Table value functions

-- ===================================================== ==========
-- 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 supported 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
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO

3. Multi-statement Table value function: the return value is a table with logic processing capabilities, but it can only be effective for small data volumes. When the data volume is large, the speed is very slow.

Copy codeThe Code is as follows:
-- Multi-statement table Value Function

-- ===================================================== ==========
-- 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 supported 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
<@ 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

4. cursor: perform the same operation on multiple data entries, just like the for loop of a program. There are several types of loop direction control, and FETCH Next is generally used.

Copy codeThe Code is as follows:
-- Schematic SQL script

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
-- Define a cursor object [merge_cursor]
-- The cursor contains the query results of [SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0.

OPEN merge_cursor
-- Open the cursor
Fetch next from merge_cursor INTO @ MasterId, @ DuplicateId
-- Get data to temporary variables
WHILE @ FETCH_STATUS = 0 -- system @ FETCH_STATUS = 0: the loop ends.
-- Perform loop Processing
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
-- Value again
END

CLOSE merge_cursor
-- Close the cursor
DEALLOCATE merge_cursor
-- Delete a cursor

[Note: The cursor must be paired. Open -- Close. Remember to delete the cursor.]

5. transaction, transactions are required.

Copy codeThe Code is as follows:
Begin tran
Update tableA
Set columnsA = 1, columnsB = 2
Where RecIs = 1
If (@ ERROR <> 0 OR @ ROWCOUNT <> 1)
Begin
Rollback tran
Raiserror ('this update table tableA has an error !! ', 16, 1)
Return
End

Insert into tableB (columnsA, columnsB) values (1, 2)
If (@ ERROR <> 0 OR @ ROWCOUNT <> 1)
Begin
Rollback tran
Raiserror ('this update table tableA has an error !! ', 16, 1)
Return
End

End
Commit