Differences between SQL Server user-defined functions and stored procedures: SQL Server Stored Procedures

Differences between SQL Server user-defined functions and stored procedures: SQL Server Stored Procedures

I. User-defined functions:

1. Table variables can be returned.
2. Many restrictions, including
The output parameter cannot be used;
Temporary tables cannot be used;
Internal operations of functions cannot affect the external environment;
The select statement cannot be used to return the result set;
Cannot update, delete, database table;
3. a scalar value or table variable must be returned.
User-Defined Functions are generally used in areas with high reusability, simple functions, and strong contention.

Ii. Stored Procedure

1. Table variables cannot be returned.
2. few restrictions. You can perform operations on database tables and return data sets.
3. You can return a scalar value or omit return.
Stored procedures are generally used to implement complex functions and data manipulation.
 
========================================================== ======================================
SQL Server Stored Procedure-instance
Instance 1: only the stored procedure of a single record set is returned.
The table of bank deposits (bankMoney) is as follows:
 
Id
UserID
Sex
Money
001
Zhangsan
Male
30
002
Wangwu
Male
50
003
Zhangsan
Male
40
 
Requirement 1: query the stored procedure of bankMoney

create procedure sp_query_bankMoneyasselect * from bankMoneygoexec sp_query_bankMoney

Note * you only need to replace the SQL statement in T-SQL with the name of the stored procedure during usage, which is convenient!
Instance 2 (passing parameters to the stored procedure ):

Add a record to the bankMoney table and query the total amount of all deposits in the table userID = Zhangsan.

Create proc insert_bank @ param1 char (10), @ param2 varchar (20), @ param3 varchar (20), @ param4 int, @ param5 int outputwith encryption --------- encrypt asinsert into bankMoney (id, userID, sex, Money) Values (@ param1, @ param2, @ param3, @ param4) select @ param5 = sum (Money) from bankMoney where userID = 'hangsan' go the SQL Server Query analyzer executes this stored procedure by: declare @ total_price intexec insert_bank '004 ', 'hangsan', 'male', 100, @ total_price outputprint 'total balance:' + convert (varchar, @ total_price) go

Here, I will refer to the three types of return values of the stored procedure (so that the users who are reading this example do not have to check the syntax ):
1. Return an integer with Return
2. Return parameters in output format
3. Recordset

Differences between return values:

Both output and return can be received using variables in a batch program, while recordset is passed back to the client that executes the batch.
Example 3: simple process with complex SELECT statements
The following stored procedure returns all authors (names provided), published books, and publishers from the join of the four tables. This stored procedure does not use any parameters.

USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au _ info_all 'AND type = 'P') drop procedure au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name FROM authors a inner join titleauthor ta ON. au_id = ta. au_id inner join titles t ON t. title_id = ta. title_id inner join publishers p ON t. pub_id = p. the pub_idGO au_info_all stored procedure can be executed using the following methods: EXECUTE au_info_all -- OrEXEC au_info_all if the procedure is the first statement in batch processing, you can use: au_info_all

Example 4: simple process with Parameters

Create procedure au_info @ lastname varchar (40), @ firstname varchar (20) ASSELECT au_lname, au_fname, title, pub_name FROM authors a inner join titleauthor ta ON. au_id = ta. au_id inner join titles t ON t. title_id = ta. title_id inner join publishers p ON t. pub_id = p. pub_id WHERE au_fname = @ firstname AND au_lname = @ lastnameGO au_info the stored procedure can be executed using the following methods: EXECUTE au_info 'dull ', 'ann' -- OrEXECUTE au_info @ lastname = 'dull ', @ firstname = 'ann '-- OrEXECUTE au_info @ firstname = 'ann', @ lastname = 'dull '-- OrEXEC au_info 'dull ', 'ann '-- OrEXEC au_info @ lastname = 'dull', @ firstname = 'ann '-- OrEXEC au_info @ firstname = 'ann ', @ lastname = 'dull 'if this process is the first statement in batch processing, you can use: au_info 'dull', 'ann '-- Orau_info @ lastname = 'dull ', @ firstname = 'ann '-- Orau_info @ firstname = 'ann', @ lastname = 'dull'

Example 5: simple process with wildcard Parameters

Create procedure au_info2 @ lastname varchar (30) = 'd % ', @ firstname varchar (18) =' % 'ASSELECT au_lname, au_fname, title, pub_nameFROM authors a inner join titleauthor ta ON. au_id = ta. au_id inner join titles t ON t. title_id = ta. title_id inner join publishers p ON t. pub_id = p. pub_idWHERE au_fname LIKE @ firstname AND au_lname LIKE @ lastnameGO au_info2 stored procedures can be executed in multiple combinations. Only some combinations are listed below: EXECUTE au_info2 -- OrEXECUTE au_info2 'wh % '-- OrEXECUTE au_info2 @ firstname = 'a %' -- OrEXECUTE au_info2 '[CK] ars [OE] n' -- OrEXECUTE au_info2 'hunter ', 'sheryl '-- OrEXECUTE au_info2 'H %','s % '= 'proc2'

Instance 6: if... else
Stored Procedures, where @ case is used as the basis for selecting update execution. if... else is used for execution, different modifications are made based on the input parameters.

-- The following is if ...... Else stored procedure: if exists (select 1 from sysobjects where name = 'student 'and type = 'U ') drop table Studentgoif exists (select 1 from sysobjects where name = 'spupdatestudent 'and type = 'P') drop proc spUpdateStudentgocreate table Student (fName nvarchar (10), fAgesmallint, fDiqu varchar (50), fTel int) goinsert into Student values ('x. x.y', 28, 'tesing', 888888) gocreate proc spUpdateStudent (@ fCase int, @ fName nvarchar (10), @ fAge smallint, @ fDiqu varchar (50 ), @ fTel int) asupdate Studentset fAge = @ fAge, -- pass 1, 2, 3. You do not need to use casefDiqu = (case when @ fCase = 2 or @ fCase = 3 then @ fDiqu else fDiqu end) to update fAge ), fTel = (case when @ fCase = 3 then @ fTel else fTel end) where fName = @ fNameselect * from Studentgo -- change only Ageexec spUpdateStudent @ fCase = 1, @ fName = n' X. x.y', @ fAge = 80, @ fDiqu = n' updat', @ fTel = 1010101 -- change Age and Diquexec spUpdateStudent @ fCase = 2, @ fName = n' X. x.y', @ fAge = 80, @ fDiqu = n'update', @ fTel = 1010101 -- change all exec spUpdateStudent @ fCase = 3, @ fName = n' X. x.y', @ fAge = 80, @ fDiqu = n'update', @ fTel = 1010101

What are the differences between SQL functions and stored procedures?

The difference is not big. The function itself has a return value,
However, the stored procedure has no return value, and can be output by setting output parameters.

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 ,...)