SQL Server Custom Function functions use introduction _mssql

A. FUNCTION:
In sqlserver2008, there are 3 custom functions: Scalar function/Inline table value function/Multiple statement table-valued function, first summed up their grammatical similarities and differences:

Same point:
1. Create the definition is the same: A, create FUNCTION f_name (incoming parameter name passed in the type of parameter) b,returns return value type C,as XOR: 1. The scalar function returns a data type value, the inline table-valued function returns a table, and the multiple statement returns a table variable (similar to the previous two union);
2. Syntax structure: scalar function and multiple statement function are to have begin,,,end, inline table value function is not;
3. Call: Scalar function to be written in dbo,function_name;

Scalar function, using the data table written in the previous article, in the [T_ Employee Information] table, look up the work number of the employee whose name is Li Yi:

Use Sql_system 
go
CREATE FUNCTION F_gonghao (@XINGMING NVARCHAR (5))
RETURNS INT 
as
BEGIN
DECLARE @GONGHAO INT
SET @GONGHAO = (SELECT Y. Work number From[t_ employee information] as y WHERE y. Name = @XINGMING) return
@GONGHAO
end< C10/>go
/* Above is a simple example of a scalar function, the following starts the call, note is in the dbo.
/SELECT [name] From[t_ employee Information] WHERE [name]= ' Li Yi '/** contrast query *
/ * Please note the observation, and contrast/
SELECT [Name],dbo. F_gonghao (' Li Yi ') as work number From[t_ employee Information] WHERE [name]= ' Li Yi ' Go

F5 under:

Inline table-valued function: He's returning a table. For example: Filter out the clocking record for all employees at 2014.2.28th:

Use Sql_system 
go
CREATE FUNCTION f_dakajilu (@RIQI DATE)
RETURNS the TABLE as return
(
select* From[t_ attendance]as k WHERE k. Date = @RIQI 
)
Go/* Note that there is no begin+end*/on his writing//
* Below is to bring date into the function/
select* From
[F_dakajilu] (' 2014/02/28 ')
Go

F5:

A multiple-statement table-valued function is the table-valued function + inline table-valued function Comprehensive version: Write a return to the day clocking records and personal information involving employees:

Use Sql_system
go CREATE FUNCTION f_d_dakajilu (@RIQI DATE)
RETURNS @TEMP_TABLE TABLE (/* Note that since it is a multiple statement, Then you have to tell the computer what the column you want to query is * *
[name]nvarchar (5) Not NULL,
[work number]int NOT NULL, [
position]nvarchar () not NULL,
[department] NVARCHAR (5) Not NULL,
[whether Night shift]nchar (1) not NULL,
[date]date not NULL,
[Day of work]float (1) NOT null
) 
/ * Above is to tell the computer you want a basic information column/
as
BEGIN
INSERT INTO @TEMP_TABLE/* This means inserting the following query results into the @temp_table variable.
SELECT y. Name, Y. Work, Y. Position, Y. department, K. Night Shift, K. Date, K. Working hours 
of the day From[t_ Employee Info] as Y CROSS JOIN [t_ attendance] as K/  * Here I am using a cross connection/
where Y. Work number =k. The work number and K. Date = @RIQI return 
end
   go
select*
From[f_d_dakajilu] (' 2014/02/28 ')
go

F5:

Two: CURSOR, you can use it when retrieving every piece of data from a complex data, like a C-language pointer, and he can experience every piece of data and updates in your datasheet. 1. New Cursor Experience data sheet (T_ employee information) data process: 1.1. Declare a read-only cursor:

Use Sql_system 
SET TRANSACTION Isolation level repeatable READ
BEGIN TRANSACTION Employee Information
DECLARE Cur_t_ Employee Information SCROLL CURSOR for
SELECT y. Name, Y. Work number, Y. Position, Y. Department From[t_ employee Information]as y order by Y. Work number ASC 
COMMIT TRANS ACTION Cursor_read_t_ Employee Information Go

1.2. Open:

OPEN GLOBAL Cur_t_ Employee information Go

1.3. Experience each piece of data:

/* Declare 4 variables to receive data from a staging cursor
/DECLARE @XINGMING NVARCHAR (3), @GONGHAO INT, @ZHIWEI NVARCHAR (a), @BUMEN NVARCHAR (8)
/ * Use global variables to find the number of rows in the current datasheet and/or
PRINT ' +cast (@ @CURSOR_ROWS as NVARCHAR (6)) + ' row data in the current datasheet. '/
* Read the first data and save it in the staging variable * *
from[cur_t_ Employee Information]into @XINGMING, @GONGHAO, @ZHIWEI, @BUMEN/
* Use @ @FETCH_ The value returned by the status to determine the cursor read in the datasheet =0 success for FETCH execution
/while (@ @FETCH_STATUS =0)
BEGIN
PRINT ' name: ' + @XINGMING + ' Work No.: ' +convert (VARCHAR (3), @GONGHAO) + ' position: ' + @ZHIWEI + ' department: ' + @BUMEN
FETCH NEXT from[cur_t_ employee information] into @XINGMING, @ Gonghao, @ZHIWEI, @BUMEN end go

F5:

1.4. Close and release after use:

/* Close
GLOBAL cur_t_ Employee Information
* Free memory/
deallocate GLOBAL cur_t_ employee Information
Go

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

Use Sql_system 
/* Below declare and open update--cursor*/
SET TRANSACTION Isolation level repeatable READ
BEGIN TRANSACTION tr_update_yg 
DECLARE cur_t_yg CURSOR for
SELECT YG. Name, YG. Work number, YG. Position, YG. Department From[t_yuangongxinxi]as YG for 
UPDATE 
OPEN GLOBAL cur_t_yg
COMMIT TRANSACTION tr_update_yg

UPDATE:

PRINT ' currently has ' +convert (VARCHAR (3), @ @CURSOR_ROWS) + ' line of data. '
FETCH NEXT From[cur_t_yg] While
(@ @FETCH_STATUS =0)
BEGIN
Update[t_yuangongxinxi] 
set[ Work number] =[]+100 WHERE Current of
cur_t_yg
FETCH NEXT from[cur_t_yg] End 

SELECT:

Turn off release:

Close global Cur_t_yg
deallocate global Cur_t_yg

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

Use Sql_system 
go
CREATE PROCEDURE pro_select_t with
RECOMPILE 
as
select* From[t_yuangongxinxi] 
Go
Execute:
Execute pro_select_t

F5:

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

Use Sql_system 
select* into a
AA 
From[t_yuangongxinxi] Go
TRUNCATE TABLE AA-- The difference between truncate and delete is that TR is more efficient than de, and de clears the log to leave the recovery record go
INSERT into AA
EXECUTE pro_select_t

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

Use Sql_system 
go
CREATE PROCEDURE pro_daup_zhiwei
@GONGHAO INT, @ZHIWEI NVARCHAR, @RETURNS NVARCHAR ( OUTPUT
as BEGIN/
* Get post information before update/
DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR (3), @XINGMING NVARCHAR
SELECT @QIAN_RETURN_ZHIWEI =aa. Position, @XINGMING =AA. Name 
FROM[AA]
WHERE aa. Work number = @GONGHAO/ 
* Update/
UPDATE[AA] 
set[position = @ZHIWEI WHERE aa. Work number = @GONGHAO 
SET @RETURNS = ' The work number is successful: ' +convert (VARCHAR (3), @GONGHAO) + ', name is: "' + @XINGMING + '", position "' +
@QIAN_RETURN_ZHIWEI + '" Update to: "' + @ZHIWEI end

EXECUTE:

DECLARE @PRINTF NVARCHAR (m);
EXECUTE Pro_daup_zhiwei, ' SQL Engineer ', @PRINTF OUTPUT 
SELECT @PRINTF as ' update message ' Go

F5:

This is where pro comes in;
four: TRIGGER, Trigger , similar to a mine, as long as you violate his request, he began to work, can also use this function to maintain or block some do not want to occur error operations, DDL: such as: Do not delete a "AA" table:

Use Sql_system 
go
CREATE TRIGGER drop_t in
DATABASE for drop_table 
as
BEGIN
ROLLBACK TRANSACTION 
PRINT ' cannot be deleted because I have added trigger protection! ' End

Drop

F5:

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

Delete operation:

Update operation:

As a simple example, the next update operation: that is, focus on the inserted and deleted table:

Use [Sql_system]
go
/****** object:trigger [dbo].[ update_t] Script date:03/04/2014 16:04:21 ******/
SET ansi_nulls on
go
SET quoted_identifier
on Go
CREATE TRIGGER [dbo]. [update_t]
on [dbo]. [AA] INSTEAD of UPDATE
as
BEGIN
INSERT into T_update_hou
select* 
from[inserted]/ 
* Inserts the updated data into the "T_update_hou"/INSERT INTO
t_update_qian
select*
from[deleted]/ 
* Inserts the data before the update into the T_ Update_qian "Medium/
PRINT" updated, the data before the update has been written to the "T_update_qian", the updated data inserted into the "T_update_hou." ' End

Update

Use Sql_system 
go
update[aa] 
SET aa. Job = ' SQL Senior Engineer '
WHERE AA. Work number =101/
* Above is a simple update operation, For UPDATE Trigger/
select* From[t_update_hou]--Modified data
select* From[t_update_qian]--data to be modified go

F5:

At the time of last year I wrote an update on the view of a multiple-table connection using trigger:

Http://www.jb51.net/article/95473.htm