The difference between stored procedure and function of SQL

1. Create functions, find functions

/*creates a function with parameters that returns a single*/CREATE FUNCTION [dbo]. [Getgrade] (@userName nvarchar (Ten), @subject nvarchar (Ten)) returns nvarchar (Ten) asbegindeclare @grade nvarchar (Ten)if@userName ='Zhang San'   Set@grade = (Select[Source] fromTestrows2columnswhere[Email protected] and [subject]=@subject)if@userName ='John Doe'   Set@grade = (Select[Source] fromTestrows2columnswhere[Email protected] and [subject]=@subject)if@userName ='Harry'   Set@grade = (Select[Source] fromTestrows2columnswhere[Email protected] and [subject]=@subject)return@gradeend

Execute function

Select dbo. Getgrade (' Zhang San ',' language ')

Returning table functions

/* Create a function that returns a table */ CREATE  FUNCTION  GETGrade2 (@userName nvarchar (ten), @subject nvarchar)RETURNS @TempTable TABLE (userName nvarchar (ten), subject nvarchar (), [Source] nvarchar (()) As  Begininsert into @TempTable (Username,[subject],[source]) SELECT [Username],[subject],[source]    from where [Email protected] and [subject]=@subjectRETURN END

Execute function

 from dbo. GETGrade2 (' Zhang San ',' language ')

Create a stored procedure, find a stored procedure

CREATE PROC p_view (@userName nvarchar (ten), @subject nvarchar ( e-mail Protected] and [subject]=@subjectEND

Execute the storage process

' John Doe ',' mathematics '

Essentially no difference. Just a function like: Only one variable can be returned with a limit. A stored procedure can return more than one. Functions can be embedded in SQL and can be called in Select, and stored procedures do not work. The essence of execution is the same.
There are many function limitations, such as the inability to use temporary tables and only table variables. There are also some functions that are not available, and so on. and stored procedures are relatively less restrictive
1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.
2. Parameters can be returned for stored procedures, and functions can only return values or table objects.
3. The stored procedure is typically performed as a separate part (exec execution), and the function can be invoked as part of a query statement (select Call), since the function can return a Table object, so it can be located in the query statement after the FROM keyword.
4. When the stored procedure and function are executed, SQL Manager will go to the procedure cache to fetch the corresponding query statement, and if there is no corresponding query in the procedure cache, SQL Manager compiles the stored procedures and functions.
The Procedure cache holds the execution plan (execution plan), executes the execution plan in the Procedure cache when it is compiled, and then SQL Server will follow each execution Plan's actual situation to consider whether or not to save the plan in the cache, the criteria for judging is the frequency at which the execution plan might be used, and secondly, the cost of generating the plan, which is the time it takes to compile. The plan saved in the cache will not be compiled the next time it executes.

The difference between stored procedure and function of SQL