SQL Server functions and stored procedures

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

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

Creating scalar-valued functions
Grammar:
CREATE FUNCTION [schema_name.] Function_name ([{@parameter_name PARAMETER_DATA_TYPE[=DEFAULT_VALUE]}[...N]]) RETURNS Return_data_type as
BEGIN
Function_body
RETURN scalar_expression
END
Syntax Description:
1, [] the contents are optional.
2. schema_name specifies the schema name of the function.
3. @parameter_name the name of the parameter for the specified function.
4. PARAMETER_DATA_TYPE specifies the data type of the parameter.
5, Default_value The default value of the specified parameter.
6. The Returns keyword specifies the return type of the function.
7, function_body specifies the function body.
8. The return statement specifies the function return value or expression.
Examples are as follows:
Use Bank
GO
CREATE FUNCTION Getaccountname
(
@account_id int---parameter

)
RETURNS varchar (20)--return varchar (20)
As
BEGIN
DECLARE @accountName varchar (20)
Select @accountName =account_name from account where [email protected]_id
Return @accountName--return value
END
GO
Using scalar-valued functions
When using scalar-valued functions, the parameters required by the function are passed in, and then the return of the scalar-valued function is passed through the SELECT statement
Value is assigned to the variable, or it can be output directly
--Direct output scalar
Select Dbo.getaccountname (1) as account name
* Note: In the process of calling a function, you must add "dbo." Before the function name. Otherwise, it is not possible to recognize whether the function is an intrinsic or a custom function.
--Storing the return value of a scalar value function in a variable
DECLARE @accountName varchar (20)
Select @accountName =getaccountname (1)
print ' account name is: ' + @accountName
Description: A variable-value function can be called by another scalar-valued function or table-valued function.

Table-Valued functions: Table-valued functions return results as data tables. Table-valued functions are powerful, even in most cases, to replace views. A view cannot define a parameter, but a table-valued function
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-valued functions:
A multi-statement table-valued function requires that the return type be a table type, which differs from a scalar-valued function in that it requires the structure of the returned table to be specified after the Returns keyword when the function is defined.
Grammar:
CREATE FUNCTION [schema_name.] Function_name ([{@parameter_name parameter_data_type [=default_value]}[,... N]])
RETURNS @table_var_name Table (table_definition) as
BEGIN
Function_body
RETURN
END
The definition syntax for a multi-statement table-valued function is two different from the definition syntax of a scalar-valued function: The first is the returns in the function declaration
is the table type and must specify the structure, and the second is the return value or expression in the function body that does not need to be written back.
Examples are as follows:
Use Bank
GO
--Returns a variable of type table after the function is executed @deposeittable
Create function Getdeposit ()
Returns @depositTable table
(
AccountName varchar (20),
Balance Float
)
As
BEGIN
--assigning values to variables of type table
INSERT INTO @depositTable
Select Account_name,balance from Account,all_purpose_card the where account. Account_id=all_purpost_card. account_id
Return--no need to return the variable directly in the write value or expression @deposittable
END
GO
When you call a table-valued function, you can use it as a normal table
Examples are as follows:
--Using table-valued functions Getdeposit
SELECT * FROM Getdeposit ()

Using table-valued functions with parameters:
Use Bank
GO
--parameters require incoming account name
Create function getdeposit (@accountName varchar)
Returns @depositTable table
(
AccountName varchar (),
Balance float
)
as
BEGIN
-- Use parameters @accountname
INSERT INTO @depositTable
Select Account_name,balance from Account,all_purpose_card in subqueries where account. Account_id=all_purpost_card. account_id and[ Email protected]
return
END
GO
Invoke multi-statement table-valued Function:
SELECT * from Getdeposit (' Lu Xun ')

Inline table-valued functions:
An inline table-valued function is a special form of a multi-statement table-valued function that is basically the same way as a multi-statement table-valued function. Two
The difference is that the inline table-valued function can have only one SELECT statement and does not need to define a variable structure that returns a table type, which can be
Returns the result of the SELECT statement directly after the return keyword.
Grammar:
CREATE FUNCTION [schema_name.] Function_name ([{@paramet_name parameter_data_type [=default_value]}[,... N]])
RETURNS TABLE as
Function_body
RETURN [select_stmt]
You do not need to define a table-type variable after the returns keyword, nor do you need a definition for the tables structure. After the return statement
Queries the data rows directly using the SELECT statement and returns the results.
Create an inline table-valued function getaccount, and accept a parameter that returns the account information based on the parameters passed in, as shown in the following example:
Use Bank
GO
Create function Getaccount (@account_id int)
Returns table
As
Return
(
SELECT * from account where [email protected]_id
)
To invoke an inline table-valued Function:
SELECT * from Getaccount (5)

Advantages of stored procedures:
1. Allow modular programming
2. Faster execution speed
3. Reduce network traffic
4, 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 report information about a specified database or all databases
Sp_renamedb Renaming a database
Sp_tables returns any object in the current environment that can appear in the FROM clause
Sp_columns viewing list information for a table
sp_help View all information for a table
Sp_helpcoonstraint viewing constraints on a table
Sp_helpindex viewing the index of a table
Sp_stored_procedure list all stored procedures in the current environment
sp_password Add or modify password for login account
sp_helptext display default values, unencrypted stored procedures, user-defined stored procedures, triggers, or actual text for a view
Sp_addrole creating a new data role to the current database
Sp_adduser Adding a new user to the current data
Sp_cmdshell using DOS commands to manipulate files and directories
Sp_logevent user-defined messages into SQL Server log File box Windows Event Viewer

User-defined stored procedures
Grammar:
CREATE Proc[edure] Proc_name
[{@parameter_name data_type}=[default]] [OUTPUT,.., N]
As
Procedure_body
Syntax Description: Procedure can be omitted as Proc,proc_name refers to the stored procedure name, followed by the stored procedure name is
The parameter list, which is optional. If you have parameters, you need to specify the data type of the parameter, and if you have a default value, you need to
Specifies the default value for the parameter. You can also specify the OUTPUT keyword for a parameter to indicate that the parameter is an outgoing parameter. As keyword
The following procedure_body indicates that the body of the stored procedure is the core of the stored procedure.
1. Create a stored procedure with no 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
Calling a stored procedure to execute with the EXEC or execute command
Use Bank
GO
EXEC proc_min_balance
2. Stored procedure with input parameters
In other languages, if the method has parameters, the actual parameter value is passed at the time of invocation.
--Create stored procedures with parameters to add account information based on incoming 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
Executing stored procedures with parameters, you need to pass the actual parameter values into the stored procedure
exec proc_account_insert ' Zhuge Liang ', ' 546646265656651 ', ' 2011-01-02 '


Stored procedure with output parameters
If you need a stored procedure to return a value or multiple values, you can use an output parameter. The output parameters must be defined in the stored procedure
Use the OUTPUT keyword when declaring
Tip: Stored procedures can also return values through return, but typically only some execution state values are returned.
Examples are as follows:
Use Bank
GO
if exists (select * from sysobjects where name= ' proc_getday ')
drop procedure Proc_getday
GO
Create proc Proc_getday
@day int output,--out 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 need to first define the corresponding variable as the actual parameter, and you must
Use the OUTPUT keyword after the actual parameter. Once the stored procedure executes successfully, it can be stored by variable
The parameter value of the procedure's outgoing.
--Define variables first, consistent with outgoing parameter types
DECLARE @day int
--When executing a stored procedure, use variables as actual parameters and describe them using the OUTPUT keyword
EXEC proc_getday @day output, ' 2012-01-03 '
--After execution, the value of the stored procedure is obtained by the variable
print ' Distance current days: ' +cast (@day as varchar (4))
GO

SQL Server functions and stored procedures