Talking about the difference between SQL server user defined function and stored procedure

Time:2021-6-18

1、 Custom function:

1. Table variables can be returned
2. There are many restrictions, including
The output parameter cannot be used;
Temporary tables cannot be used;
The operation inside the function cannot affect the external environment;
The result set cannot be returned by select;
Cannot update, delete, database table;
3. Must return a scalar value or table variable
User defined functions are generally used in places with high reusability, simple and single function and strong contention.

2、 Stored procedure

1. Table variables cannot be returned
2. Less restrictions, can perform operations on database tables, can return data sets
3. You can return a scalar value or omit return
Stored procedure is generally used to realize complex functions and data manipulation.
 
=========================================================================
Sqlserver stored procedure — an example
Example 1: a stored procedure that returns only a single recordset.
Table the contents of bank money are 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 the content of bankmoney


create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney

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

Add a record to the bankmoney table and query the total amount of all deposits with userid = Zhangsan in the table.

Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
With encryption
as
insert into bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
go
The method of executing the stored procedure in SQL Server Query Analyzer is as follows:
declare @total_price int
exec insert_ Bank '004', 'Zhangsan', 'male' ,100,@total_ price output
Print'the total balance is' + convert( varchar,@total_ price)
go

Let’s talk about the three return values of stored procedures (for the convenience of friends who are looking at this example, they don’t need to look at the syntax content any more)
1. Return an integer with return
2. Return parameters in output format
3.Recordset

Difference of return value:

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

USE pubs
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'au_info_all' AND type = 'P')
  DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
  FROM authors a INNER JOIN titleauthor ta
   ON a.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
GO
  au_ info_ The all stored procedure can be executed in the following ways:
  EXECUTE au_info_all
-- Or
EXEC au_info_all
  If the procedure is the first statement in the batch, you can use:
  au_info_all

Example 4: using a simple procedure with parameters

CREATE PROCEDURE au_info
  @lastname varchar(40),
  @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
  FROM authors a INNER JOIN titleauthor ta
   ON a.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 = @lastname
GO
  au_ The info stored procedure can be executed in the following ways:
  EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
  If the procedure is the first statement in the batch, you can use:
  au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'

Example 5: using a simple procedure with wildcard parameters

CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
  ON a.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 LIKE @firstname
  AND au_lname LIKE @lastname
GO
  au_ Info2 stored procedures can be executed in a variety of combinations. Only some combinations are listed below:
  EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'
  = 'proc2'

Example 6: if… Else
Stored procedure, where @ case is used as the selection basis for executing update, and if… Else is used to implement different modifications according to the incoming parameters

--Here is the stored procedure of if... Else:
if exists (select 1 from sysobjects where name = 'Student' and type ='u' )
drop table Student
go
if exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' )
drop proc spUpdateStudent
go
create table Student
(
fName nvarchar (10),
fAge
smallint ,
fDiqu varchar (50),
fTel int
)
go
insert into Student values ('X.X.Y' , 28, 'Tesing' , 888888)
go
create proc spUpdateStudent
(
@fCase int ,
@fName nvarchar (10),
@fAge smallint ,
@fDiqu varchar (50),
@fTel int
)
as
update Student
Set Fage = @ Fage, - pass 1,2,3 to update Fage, do not need to use case
fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),
fTel = (case when @fCase = 3 then @fTel else fTel end )
where fName = @fName
select * from Student
go
--Only age
exec spUpdateStudent
@fCase = 1,
@fName = N'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel = 1010101
--Change age and Diqu
exec spUpdateStudent
@fCase = 2,
@fName = N'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel = 1010101
--Total reform
exec spUpdateStudent
@fCase = 3,
@fName = N'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel = 1010101