1. Scalar function: The result is a single value that can contain the logical processing process. It is not possible to use nondeterministic system functions such as GETDATE ().
Copy Code code 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 won't 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
--Declare The return variable
DECLARE < @ResultVar, sysname, @Result > <function_data_type,,int>
--ADD the T-SQL statements to compute "return value" here
SELECT < @ResultVar, sysname, @Result > = < @Param1, sysname, @p1 >
--Return the result of the function
return < @ResultVar, sysname, @Result >
End
2. Inline table-valued Function: The return value is a table, implemented by only one SQL statement, and has no logical processing power. A query that performs large amounts of data.
Copy Code code as follows:
--inline table-valued function
-- ================================================
--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 won't 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
As
Return
(
--ADD The SELECT statement with parameter references
SELECT 0
)
Go
3. Multi-statement table-valued Function: The return value is a table, has the logical processing ability, but can only be effective to the small data quantity data, the data quantity is big, the speed is very slow.
Copy Code code as follows:
--Multi-statement table-valued 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 won't 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
< @Table_Variable_Name, sysname, @Table_Var > Table
(
--ADD The column definitions for the TABLE variable
<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. Cursors: Perform the same operation on multiple data. Like a For loop for a program. There are several loop direction controls, generally with fetch Next.
Copy Code code 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 I smerged = 0
--Define a Cursor object [Merge_cursor]
--the cursor contains the result of the query [SELECT mastercustomerid, Duplicatecustomerid from duplicatecustomers WHERE ismerged = 0].
OPEN Merge_cursor
--Open cursor
FETCH NEXT from Merge_cursor into @MasterId, @DuplicateId
--take data to a temporary variable
While @ @FETCH_STATUS = 0-system @ @FETCH_STATUS = 0 o'clock Loop End
--Doing the recycling process
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
--Take the value again
End
Close Merge_cursor
--Close cursor
Deallocate merge_cursor
--Delete cursor
[Description: Cursor use must be paired, Open--close, and finally remember to delete the cursor.]
5. Transactions: When there are multiple operations in a single processing, or all operations, or all do not operate, the operation failed one, all the others to be undone, regardless of whether the other successful execution, then need to use the transaction.
Copy Code code as follows:
Begin TRAN
Update TableA
Set columnsa=1,columnsb=2
where Rec Is=1
if (@ @ERROR <> 0 OR @ @ROWCOUNT <> 1)
Begin
Rollback TRAN
RAISERROR (' This update table tabl EA Error!! ', 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 error!! ', 16, 1)
Return
End
End
Commit
