SQL Server Functions and stored procedures, SQL Server Stored Procedures

SQL Server Functions and stored procedures, SQL Server Stored Procedures

The SQL Server function is a structure that encapsulates one or more SQL statements.
SQL Server functions are divided into system functions and user-defined functions.

Scalar function: the return value of a scalar function is a single value or a value expression of the basic data type.
The function body can be either a statement or multiple statements.

Create scalar function
Syntax:
Create function [schema_name.] function_name ([{@ parameter_name parameter_data_type [= default_value]} [... n]) RETURNS return_data_type
BEGIN
Function_body
RETURN scalar_expression
END
Syntax description:
1. The content in [] is optional.
2. schema_name specifies the schema name of the function.
3. @ parameter_name specifies the parameter name of the function.
4. parameter_data_type specifies the Data Type of the parameter.
5. default_value specifies the default value of the parameter.
6. The RETURNS keyword specifies the function return type.
7. function_body specifies the function body.
8. The RETURN statement specifies the function RETURN value or expression.
Example:
USE Bank
GO
Create function getAccountName
(
@ Account_id int --- Parameter

)
RETURNS varchar (20) -- RETURNS varchar (20)
AS
BEGIN
DECLARE @ accountName varchar (20)
Select @ accountName = account_name from Account where account_id = @ account_id
RETURN @ accountName -- RETURN Value
END
GO
Use scalar functions
When using a scalar value function, first pass in the required parameters of the function, and then use the SELECT statement to return the Scalar Value Function
The value is assigned to a variable, and can be output directly.
-- Directly output scalar
Select dbo. getAccountName (1) as account name
* Note: "dbo." Must be added before the function name during function calling .". Otherwise, you cannot identify whether the function is an internal function or a custom function.
-- Save the return value of the scalar value function to the variable
DECLARE @ accountName varchar (20)
Select @ accountName = getAccountName (1)
Print 'account name: '+ @ accountName
Note: A variable value function can be called by another scalar value function or table value function.

Table value function: The result returned by the table value function is a data table. Table-valued functions are powerful and can replace views in most cases. The view cannot define parameters, but the table Value Function
It can be used as a view with parameters. Table valued functions can be divided into multi-statement table valued functions and inline table valued functions.
1. Multi-statement Table value functions:
A multi-statement TABLE valued function requires that the return type be TABLE. The difference between the return type and the scalar value function is that when the function is defined, the structure of the returned TABLE must be specified after the RETURNS keyword.
Syntax:
Create function [schema_name.] function_name ([{@ parameter_name parameter_data_type [= default_value]} [,... n])
RETURNS @ table_var_name TABLE (table_definition)
BEGIN
Function_body
RETURN
END
The definition Syntax of a Multi-statement table valued function differs from the definition Syntax of a scalar function. First, the definition syntax after RETURNS in a function declaration
It is a TABLE type and the structure must be specified. Second, RETURN in the function body does not need to write the returned value or expression.
Example:
USE Bank
GO
-- After the function is executed, return the TABLE Type Variable @ deposeitTable.
Create function getDeposit ()
Returns @ depositTable table
(
AccountName varchar (20 ),
Balance float
)
AS
BEGIN
-- Assign values to TABLE-type variables
Insert into @ depositTable
Select account_name, balance from account, all_purpose_card where account. ACCOUNT_ID = all_purpost_card.ACCOUNT_ID
Return -- directly return the variable @ depositTable without writing a value or expression.
END
GO
When calling a table value function, you can use it as a normal table.
Example:
-- Use the table value function getDeposit
Select * from getDeposit ()

Use a table-valued function with parameters:
USE Bank
GO
-- The parameter must be input into the account name
Create function getDeposit (@ accountName varchar (20 ))
Returns @ depositTable table
(
AccountName varchar (20 ),
Balance float
)
AS
BEGIN
-- Use the @ accountName parameter in the subquery
Insert into @ depositTable
Select account_name, balance from account, all_purpose_card where account. ACCOUNT_ID = all_purpost_card.ACCOUNT_ID andaccount_name = @ accountName
Return
END
GO
Call the multi-statement Table value function:
Select * from getDeposit ('luxon ')

Inline Table value functions:
Inline table valued functions are a special form of Multi-statement table valued functions. They are created in the same way as multi-statement table valued functions. Both
The difference is that an inline TABLE-valued function can have only one SELECT statement and does not need to define the variable structure of the returned TABLE type.
RETURN the result of the SELECT statement directly after the RETURN keyword.
Syntax:
Create function [schema_name.] function_name ([{@ paramet_name parameter_data_type [= default_value]} [,... n])
RETURNS TABLE
Function_body
RETURN [select_stmt]
The RETURNS keyword does not need to be followed by TABLE-type variables or TABLE structure definitions. After the RETURN statement
Use the SELECT statement to query data rows and return results.
Create an inline Table value function getAccount and accept a parameter. The account information is returned based on the input parameter. The example is as follows:
USE Bank
GO
Create function getAccount (@ account_id int)
Returns table
AS
Return
(
Select * from Account where account_id = @ account_id
)
Call the inline Table value function:
Select * from getAccount (5)

Advantages of stored procedures:
1. Modular programming is allowed
2. Faster execution
3. reduce network traffic
4. It can be used as a security mechanism
Common system stored procedures

System Stored Procedure Name Description
Sp_databases: list all databases on the server
Sp_hepdb reports information about a specified database or all databases
Sp_renamedb rename the database
Sp_tables: returns any objects that can appear in the FROM clause in the current environment.
Sp_columns
Sp_help
Sp_helpcoonstraint
Sp_helpindex
Sp_stored_procedure: list all stored procedures in the current environment
Sp_password: Add or modify the Logon account password
Sp_helptext displays the default value, unencrypted stored procedure, user-defined stored procedure, trigger, or view's actual text
Sp_addrole create a data role in the current database
Sp_adduser: Add a new user to the current data
Sp_cmdshell uses the doscommand to operate files and directories
Sp_logevent records user-defined messages in the SQL Server log file box Windows Event Viewer

Custom Stored Procedure
Syntax:
Create proc [EDURE] proc_name
[{@ Parameter_name data_type} = [default value] [OUTPUT,..., n]
AS
Procedure_body
Syntax description: PROCEDURE can be omitted as PROC. proc_name indicates the name of the stored PROCEDURE.
Parameter List, which is optional. If a parameter exists, you must specify the Data Type of the parameter. If a default value exists, you must
The default value of the specified parameter. You can also specify the OUTPUT keyword for the parameter, indicating that the parameter is an OUTPUT parameter. AS keyword
Procedure_body indicates that the subject of the stored procedure is the core of the stored procedure.
1. Create a stored procedure without Parameters
USE Bank
GO
If exists (select * from sysobjects where name = 'proc _ min_balance ')
Drop procedure proc_min_balance
GO
Create proc_min_balance
AS
Select ACCOUNT_NAME from ACCOUNT where ACCOUNT_ID =
(
Select top 1 ACCOUNT_ID from ALL_PURPOSE_CARD order by BALANCE

)
GO
The stored procedure is called through EXEC or EXECUTE Command
USE Bank
GO
Exec proc_min_balance
2. Stored Procedures with input parameters
In other languages, if the method has parameters, the actual parameter value must be passed during the call.
-- Create a stored procedure with parameters and add account information based on input data
USE Bank
GO
If exists (select * from sysobjects where name = 'proc _ Account_Insert ')
Drop procedure proc_Account_Insert
GO
Create proc proc_Account_Insert
@ Account_Name varchar (20 ),
@ Code varchar (18 ),
@ Open_time datetime
AS
Insert into ACCOUNT values (@ Account_Name, @ code, @ open_time)
If (@ ERROR = 0)
Print 'OK'
Else
Print 'error'
GO
To execute a stored procedure with parameters, you must pass the actual parameter values into the stored procedure.
Exec proc_Account_Insert 'zhuge liang', '20170101', '2017-01-02'


Stored Procedure with output parameters
You can use output parameters to return one or more values in a stored procedure. Output parameters must be defined in the Stored Procedure
Use the OUTPUT keyword to declare
Tip: stored procedures can also RETURN values through RETURN, but usually only some execution Status values are returned.
Example:
USE Bank
GO
If exists (select * from sysobjects where name = 'proc _ getday ')
Drop procedure proc_getDay
GO
Create proc proc_getDay
@ Day int output, -- output parameter, returns the number of days
@ Date datetime
AS
Select @ day = datediff (day, @ date, CETDATE ())
GO
When calling a stored procedure with an outgoing function, you must first define the corresponding variable as the actual parameter.
Use the OUTPUT keyword after the actual parameter. After the stored procedure is successfully executed, you can get the stored data through the variables.
Parameter value passed out of the process.
-- Define the variable first, which is consistent with the output parameter type
Declare @ day int
-- When executing the stored procedure, the variable is used as the actual parameter and the OUTPUT keyword is used for description.
Exec proc_getDay @ day output, '2017-01-03'
-- Get the output value of the stored procedure through the variable after execution.
Print 'current days: '+ cast (@ day as varchar (4 ))
GO

How to call user-defined functions in SQL Server Stored Procedures

A function that returns a single value. It can be used as a variable, for example, select dbo. function Name (parameter 1, parameter 2 ,...) from... where abc = dbo. function Name (parameter 1, parameter 2 ...)
The function that returns the table can be used as a data table. For example, select * from dbo. Function Name (parameter 1, parameter 2 ,...)

In SQL, what is the difference between a function and a stored procedure?

The function has a return value, and the process has no return value.