Talking about the difference between SQL Server custom functions and stored procedures _mssql

One, Custom function:

1. Can return the table variable
2. There are a lot of restrictions, including
Cannot use output parameters;
Cannot use temporary tables;
The operation inside the function cannot affect the external environment;
The result set cannot be returned through a select;
Cannot update,delete, database table;
3. Must return a scalar value or a table variable
Custom functions are generally used in a high degree of reusability, simple function, competing for strong places.

Second, stored procedures

1. Cannot return table variable
2. Less restrictive, you can perform operations on a database table, you can return a dataset
3. You can return a scalar value, or you can omit return
Stored procedures are generally used in the implementation of complex functions, data manipulation.

=========================================================================
SQL Server stored procedure--instance
Instance 1: A stored procedure that returns only a single recordset.
Table the contents of the Bank deposit form (Bankmoney) are 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 use of the process only need to replace the SQL statements in T-SQL to stored procedure name, it is convenient!
Instance 2 (passing parameters to stored procedures):

Add a record to the table Bankmoney and query the total amount of all deposits userid= zhangsan in this table.

Create proc Insert_bank @param1 char (@param2 varchar), @param3 varchar, @param4 int, @param5 int output
With encryption---------encryption
as insert into Bankmoney (Id,userid,sex,money)
Values (@param1, @param2, @ PARAM3, @param4)
select @param5 =sum from Bankmoney where userid= ' Zhangsan ' Go
in sql The method for executing the stored procedure in Server Query Analyzer is:
declare @total_price int
exec insert_bank ' 004 ', ' Zhangsan ', ' Man ', @total_price Output
print ' Total balance is ' +convert (varchar, @total_price)
go

Here's a little bit of a second. 3 return values for the stored procedure (convenient for those who are looking at this example no longer have to look at the grammatical content):
1. Returns an integer with return
2. Returns parameters in output format
3.Recordset

The difference between return values:

Both output and return can be received in a batch program and the recordset is returned to the client of the execution batch.
Example 3: Using a simple procedure with a complex SELECT statement
The following stored procedure returns all authors (names), published books, and publishers from a four-table join. The stored procedure does not use any parameters.

Use the pubs
IF EXISTS (SELECT name from sysobjects
     WHERE name = ' Au_info_all ' and type = ' P ')
  DROP PROCEDURE au_i Nfo_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 J OIN 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,
  @firstname varchar as
SELECT au_lname, au_fname, Title, pub_name from
  authors a INNER join titleauthor ta in
   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 = @l Astname
Go au_info stored procedures can be performed in the following ways:
execute au_info ' dull ', ' Ann '
--Or
execute au_info @last Name = ' Dull ', @firstname = ' ann '
--or
EXECUTE au_info @firstname = ' ann ', @lastname = ' dull '
--or
E  Xec 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 () = '% ' as
SELECT au_lname , au_fname, title, pub_name from
authors a INNER join titleauthor ta
  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 as @firstname
  an D au_lname like @lastname
go au_info2 stored procedures can be executed in a variety of combinations. Only some of the 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 '
   

Instance 6:if...else
A stored procedure in which @case performs a different modification based on the parameters passed in, using the If...else implementation as a selection basis for executing the update.

--The following is the if......else stored procedure: if exists (select 1 from sysobjects where name = ' Student ' and type = ' u ') drop table Student go if E Xists (select 1 from sysobjects where name = ' spupdatestudent ' and type = ' P ') drop proc spupdatestudent go create table S Tudent (fName nvarchar), Fage smallint, fdiqu varchar (m), Ftel int) go INSERT into Student values (' x.x.y ', 28, ' Tesing ', 888888] go create proc spupdatestudent (@fCase int, @fName nvarchar (a), @fAge smallint, @fDiqu varchar (50 ), @fTel int) as update Student set Fage = @fAge,--1,2,3 to be updated Fage do not need to use case fdiqu = (case @fCase = 2 or @fCase = 3 then @fDiqu else Fdiqu end), Ftel = (case @fCase = 3 then @fTel else Ftel end) Where fName = @fName Select * FR Om Student Go--change only the age exec spupdatestudent @fCase = 1, @fName = N ' X. X.y ', @fAge =, @fDiqu = N ' Update ', @fTel = 1010101--Change age and Diqu exec spupdatestudent @fCase = 2, @fName = N ' X. X.y ', @fAge =, @fDiqu = N ' Update ', @fTel = 1010101--All change exec spupdatestudeNT @fCase = 3, @fName = N ' X. X.y ', @fAge =, @fDiqu = N ' Update ', @fTel = 1010101