Example of the difference between SQL Server custom functions and stored procedures

share the differences between SQL Server custom functions and stored procedures, and learn together.

One, Custom function:
1. You can return table variables
2. There are a lot of restrictions, including
Output parameters cannot be used;
Temporary tables cannot be used;
The operation inside the function cannot affect the external environment;
Cannot return result set through select;
Not update,delete, database tables;
3. Must return a scalar value or table variable
Custom functions are generally used in a high degree of reuse, simple function, the fight against the strong place.
Second, the stored procedure
1. Cannot return table variable
2. Fewer restrictions, you can perform operations on database tables, you can return datasets
3. You can return a scalar value, or you can omit the return
Stored procedures are generally used in the implementation of complex functions, data manipulation.

=========================================================================
SQL Server stored Procedures-instance
Example 1: A stored procedure that returns only a single record set.
Table Bank deposit form (Bankmoney) is as follows

Id
Userid
Sex
Money
001
Zhangsan
Man
30
002
Wangwu
Man
50
003
Zhangsan
Man
40

Requirement 1: A stored procedure that queries the contents of a table Bankmoney
CREATE PROCEDURE Sp_query_bankmoney
As
SELECT * FROM Bankmoney
Go
EXEC Sp_query_bankmoney
Note * In the process of using only need to replace the SQL statements in T-SQL with the name of the stored procedure, it is very convenient!
Instance 2 (passing parameters to the stored procedure):
Add a record to table Bankmoney and query the total amount of all deposits userid= zhangsan in this table.
Create proc Insert_bank @param1 Char (TEN), @param2 varchar, @param3 varchar, @param4 int, @param5 int output
With encryption---------encryption
As---www.jbxue.com
Insert into Bankmoney (Id,userid,sex,money)
Values (@param1, @param2, @param3, @param4)
Select @param5 =sum (Money) from Bankmoney where userid= ' Zhangsan '
Go
The way to execute the stored procedure in SQL Server Query Analyzer is:
DECLARE @total_price int
EXEC insert_bank ' 004 ', ' Zhangsan ', ' male ', @total_price output
print ' Total balance is ' +convert (varchar, @total_price)
Go
Let's go over the 3 return values of the stored procedure (it is convenient for friends who are looking at this example not to look at the syntax):
1. return integers as returns
2. Returning parameters in output format
3.Recordset
The difference between return values:
Both output and return can be received in the batch program, and the recordset is passed back to the client that executed the batch.
Example 3: Using a simple procedure with a complex SELECT statement
The following stored procedure returns all authors (with names), published books, and publishers from a join of 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_all stored procedures can be performed in the following ways:
EXECUTE Au_info_all
--Or
EXEC Au_info_all
If the procedure is the first statement in a 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_info stored procedures can be performed 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 a 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 = ' 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 a few 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 procedures, where @case is used as the selection basis for performing the update, and performs different modifications according to the parameters passed in If...else implementation.
--Here is the stored procedure for 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 ', ' tesing ', 888888)
Go
Create proc Spupdatestudent
(
@fCase int,
@fName nvarchar (10),
@fAge smallint,
@fDiqu varchar (50),
@fTel int
)
As
Update Student
Set Fage = @fAge,---------To update Fage no case required
Fdiqu = (case is @fCase = 2 or @fCase = 3 then @fDiqu else Fdiqu end),
Ftel = (case if @fCase = 3 then @fTel else Ftel end)
where fName = @fName
SELECT * FROM Student
Go
--Just change the age
EXEC spupdatestudent
@fCase = 1,
@fName = N ' x.x.y ',
@fAge = 80,
@fDiqu = N ' Update ',
@fTel = 1010101
--Change of age and Diqu
EXEC spupdatestudent
@fCase = 2,
@fName = N ' x.x.y ',
@fAge = 80,
@fDiqu = N ' Update ',
@fTel = 1010101
--Full change
EXEC spupdatestudent
@fCase = 3,
@fName = N ' x.x.y ',
@fAge = 80,
@fDiqu = N ' Update ',
@fTel = 1010101

SQL Server custom functions differ from stored procedures in a detailed example