SQL Server Custom Functions function

A. Function: There are 3 custom functions in sqlserver2008: scalar function/Inline table-valued function/multi-statement table-valued function, first summarizes their grammatical similarities and differences: The same point: 1. Create definition is the same:                                          A, CREATE FUNCTION f_name (passed parameter name     type of incoming parameter)                                          b,returns          return value type                                          C,as: 1. The scalar function returns a data type value, the inline table-valued function returns a table, and a multi-statement returns a table variable (similar to the previous two combinations);             2. Structure of the syntax: scalar functions and multi-statement functions are to have begin,,,end, the inline table-valued function is not;             3. Call: Scalar function to be written in dbo,function_name;
Scalar function, using the data sheet written in the previous article, check the employee name in the [T_ Staff Information] table for the worker number of the Li Yi employee:

1 Use Sql_system  2 GO 3 CREATE FUNCTION F_gonghao (@XINGMING NVARCHAR (5)) 4 RETURNS INT  5 as 6 BEGIN 7 DECLARE @GON Ghao INT 8 SET @GONGHAO = (SELECT y.  From[t_ employee Information] as y WHERE y. Name [email protected]) 9 RETURN @GONGHAO10 END11 GO12 /* Above is a simple example of a scalar function, the following is called, notice is in dbo. */13 SELECT [name] From[t_ employee Information] WHERE [name]= ' Li Yi '  /** Compare the query */14/* Please observe, and compare */15 SELECT [Name],dbo. F_gonghao (' Li Yi ') as work number From[t_ employee Information] WHERE [name]= ' Li Yi ' + GO

F5 under:
Inline table-valued functions: He returned a table, such as: Filter out 2014.2. Number 28th all employees ' punch-in records:

1 Use Sql_system  2 GO 3 CREATE FUNCTION f_dakajilu (@RIQI DATE) 4 RETURNS TABLE 5 as 6 RETURN (7 select* from[t_ attendance]as K WHERE K. Date = @RIQI  8) 9 GO10/* Note that there is no BEGIN+END*/11/* in his writing. The following is a date into the function */12 select*13 from [F_dakajilu] (' 2014/02 /28 ') + GO

F5:

A multi-statement table-valued function is a table-valued function + an inline table-valued function integrated version: Write a return on the day of the clock recording and the personal information involving employees:

1 Use Sql_system  2 GO 3 CREATE FUNCTION f_d_dakajilu (@RIQI DATE) 4 RETURNS @TEMP_TABLE TABLE (/   * Note that since it is a multi-statement, it You're going to tell the computer what column you're querying. */5 [name]nvarchar (5) not NULL, 6 [work number]int NOT NULL, 7 [position]nvarchar (TEN) not NULL, 8 [Department]nvarchar (5) Not NUL L, 9 [whether the Night Shift]nchar (1) Not null,10 [date]date not null,11 [Day work Time]float (1) not NULL12)   13/* Above is to tell the computer you want a basic information column */14 AS15 B EGIN16 INSERT INTO @TEMP_TABLE/    * This means inserting the following query results into the @temp_table variable */17 SELECT y. Name, Y. Job number, Y. department, K. Night Shift, K. Date, K. Day Hours of work From[t_ employee information] as Y Cross join [T_ attendance] as K/     * Here I use a crossover connection */19 where Y. Work number =k. Work number and K. Date = @RIQI RETURN END22 G O23 select*24 From[f_d_dakajilu] (' 2014/02/28 ') GO

F5:

Two: The cursor, when you want to retrieve every piece of data of complex data, you can use it, like a C language pointer, he can experience every data and update in your data table. 1. A new data process for the cursor Experience data sheet (T_ employee information): 1.1. Declare a read-only cursor:

1 Use Sql_system 2 SET TRANSACTION Isolation level repeatable READ3 BEGIN TRANSACTION cursor_read_t_ Employee Information 4 DECLARE cur_t_ Employees Information SCROLL CURSOR FOR5 SELECT y. Name, Y. Work number, Y. Position, Y. Department  From[t_ Employee Information]as y ORDER by Y. Work number ASC 6 COMMIT TRANSACTION Cursor_rea D_t_ Employee Information 7 GO

1.2. Open:

1 OPEN GLOBAL Cur_t_ Employee Information 2 GO

1.3. Experience each piece of data:

1/* Declares 4 variables to receive data from a staging cursor */2 DECLARE @XINGMING NVARCHAR (3), @GONGHAO INT, @ZHIWEI NVARCHAR (Ten), @BUMEN NVARCHAR (8) 3/* Global variables are used to calculate the number of rows in the current data table and/* 4 PRINT ' +cast (@ @CURSOR_ROWS as NVARCHAR (6) + ') + ' row data in the current data table. ' 5 */* Read the first data and deposit in the temporary variable */6 FETCH from[cur_t_ Employee Information]into @XINGMING, @GONGHAO, @ZHIWEI, @BUMEN 7/* Use @ @FETCH_ Status returns the value to determine if the cursor is read in the data table =0 for fetch execution */8 while (@ @FETCH_STATUS =0) 9 BEGIN10 PRINT ' name: ' + @XINGMING + ' work number: ' +convert (VARCHAR (3), @GONGHAO) + ' position: ' + @ZHIWEI + ' department: ' + @BUMEN11 FETCH NEXT from[cur_t_ employee information] into @XINGMING, @GONGHAO, @ZHIWEI, @ BUMEN12 END13 GO

F5:

1.4. Close and release after use:

1/* Close */2 Close GLOBAL Cur_t_ Employee Information 3/* Free memory */4 deallocate GLOBAL Cur_t_ Employee Information 5 GO

This allows you to read the function of each record and then use it to update the relevant data bar (all the work number values +100):

1 Use Sql_system 2/* Below declare and open UPDATE--CURSOR*/3 SET TRANSACTION isolation level repeatable READ4 BEGIN TRANSACTION tr_update _yg 5 DECLARE cur_t_yg  CURSOR FOR6 SELECT yg. Name, YG. Jobs, YG. Department  From[t_yuangongxinxi]as YG for 7 UPDATE 8 OPE N GLOBAL cur_t_yg9 COMMIT TRANSACTION tr_update_yg

UPDATE:

1 PRINT ' currently has ' +convert (VARCHAR (3), @ @CURSOR_ROWS) + ' bar data rows. ' 2 FETCH NEXT from[cur_t_yg]3 while (@ @FETCH_STATUS =0) 4 BEGIN5 Update[t_yuangongxinxi] 6 set[work number] =[work number]+1007 WHERE current O F Cur_t_yg8 FETCH NEXT From[cur_t_yg] 9 END

SELECT:

To close the release:

1 CLOSE Global cur_t_yg2 deallocate global CUR_T_YG

Three: PROCEDURE, a stored procedure is a program written in the Transact-SQL language that is provided by the server, and it can also invoke its stored procedure in a high-level language. 3.1. No parameters: Say a call to query data Table Pro:

1 Use Sql_system 2 GO3 CREATE PROCEDURE pro_select_t4 with RECOMPILE 5 AS6 select* From[t_yuangongxinxi] 7 GO8  EXECUTE : 9 EXECUTE pro_select_t

F5:

3.1.2.EXECUTE pro_select_t Here is actually a view, you can also insert the EXECUTE call result into the new table:

1 Use Sql_system  2 select* 3 to AA  4 From[t_yuangongxinxi]  5 GO 6 TRUNCATE TABLE AA  --truncate differs from delete That is, TR is more efficient than de, and the de clears the log to leave the recovery record 7 go 8 INSERT into AA 9 EXECUTE pro_select_t10 Go

The result is no, 3.2. With the reference Pro, write a modified output modified information: For example, the company's employees often have a position change, write this aspect, give the change employee's work number can modify the position of the employee, and then the modified operation feedback:

1 Use Sql_system  2 GO 3 CREATE PROCEDURE Pro_daup_zhiwei 4 @GONGHAO INT, @ZHIWEI NVARCHAR (Ten), @RETURNS NVARCHAR PUT 5 as 6 BEGIN 7/* Get job information before update */8 DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR (Ten), @XINGMING NVARCHAR (3) 9 SELECT @QIAN_RETURN_Z Hiwei =aa. Position, @XINGMING =aa. Name of the from[aa]11 where AA. Work number = @GONGHAO 12/* Update */13 UPDATE[AA] set[post] = @ZHIWEI where AA. Work number = @GONGHAO Set @RETURNS = ' The work number has been successfully: ' +convert (VARCHAR (3), @GONGHAO) + ' ", Name:" ' + @XINGMING + ' ", position" ' +16 @QIAN_RETURN_ zhiwei+ ' "updated to:" ' + @ZHIWEI17 END

EXECUTE:

1 DECLARE @PRINTF NVARCHAR, 2 EXECUTE pro_daup_zhiwei 101, ' SQL Engineer ', @PRINTF OUTPUT 3 SELECT @PRINTF as ' update message ' 4 GO

F5:

The pro is here;
Four: TRIGGER, trigger, similar to a mine, as long as you offend his request, he began to work, you can also use this function to maintain or block some do not want to occur error operations, DDL: Do not delete a "AA" table:

1 Use Sql_system 2 GO3 CREATE TRIGGER drop_t4 on DATABASE for drop_table 5 AS6 BEGIN7 ROLLBACK TRANSACTION 8 PRINT ' cannot be deleted because For I have added the trigger protection! ' 9 END

Drop

F5:

DML, which solves the problem with the lowest layer of data: There are 2 temporary tables here:deleted and inserted logic (concept) tables, to understand how trigger works: Insert operation:

Delete operation:

When the update operation occurs:

For example, let's take a look at the update operation: The focus is on the inserted and deleted tables:

1 use [Sql_system] 2 GO 3/****** Object:  Trigger [dbo].[ update_t]    Script date:03/04/2014 16:04:21 ******/4 SET ANSI_NULLS on 5 go 6 SET QUOTED_IDENTIFIER on 7 go 8 CREATE T rigger [dbo]. [update_t] 9 on [dbo]. [AA] INSTEAD of UPDATE10 AS11 BEGIN12 insert INTO T_update_hou13 select* (from[inserted] 15/* Inserts the updated data into the "T_update_hou" */16 I Nsert into  t_update_qian17 select*18 from[deleted] 19/* Insert pre-update data into "T_update_qian" */20 PRINT ' update completed, data before update has been written to ' T_ Update_qian ", the updated data is inserted into" T_update_hou ". ' END

Update

1 Use Sql_system 2 GO3 UPDATE[AA] 4 SET AA. Position = ' SQL Senior Engineer ' 5 WHERE AA. Work Number = 1016/* Above is a simple update operation for UPDATE trigger */7 select* FROM[T_UPD Ate_hou]--Modified data 8 select* From[t_update_qian]--Data to be modified 9 GO

F5:

Last year, I wrote a new operation that uses trigger to update a view of a multi-table connection:

Http://www.cnblogs.com/liyifeng/archive/2013/05/05/3056968.html

SQL Server Custom Functions function