Difference Between Stored Procedure And Functions In SQLServer

The stored procedures are the pre-compiled objects, which are compiled for the first time. Subsequently, its compiled format is saved, which executes, whenever it is called but the function is compiled and executed every time, when it is called.

Stored Procedure	Functions
1.Can have both input / output parameters	1.Can have only input parameters
2. Stored procedure can’t call from the functions	2.Functions can call from the stored procedures
3.It’s optional to return the value, as somestimes it returns 0 or n values	3.It’s should return the value
4. It allows insert/update/delete/select	4.It allows only select statement
5.Procedures cant be utilized in a select statement	5.Function is embedded in a select statement
6.Procedure cannot be used in SQL statements with WHERE/HAVING/SELECT	6.SQL statements can be used with WHERE/HAVING/SELECT
7. Exception can be handled by try-catch block	7.Here, we can’t handle the exception, using try-catch block

StoredProcedure

Syntax: 

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name

[ @parameter [type_schema_name.] datatype

[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]

, @parameter [type_schema_name.] datatype

[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]

[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]

[ FOR REPLICATION ]

AS

BEGIN

[declaration_section]

executable_section

END;

 

Description:

 

schema_name

The name of the schema that owns the stored procedure.

procedure_name

The name to assign to this procedure in SQL Server.

@parameter

One or more parameters passed into the procedure.

type_schema_name

The schema that owns the data type, if applicable.

datatype

The data type for @parameter.

VARYING

It is specified for cursor parameters when the result set is an output parameter.

default

The default value to assign to @parameter.

OUT

It means that @parameter is an output parameter.

OUTPUT

It means that @parameter is an output parameter.

READONLY

It means that @parameter can not be overwritten by the stored procedure.

ENCRYPTION

It means that the source for the stored procedure will not be stored as plain text in the system views in SQL Server.

RECOMPILE

It means that a query plan will not be cached for this stored procedure.

EXECUTE AS clause

It sets the security context to execute the stored procedure.

FOR REPLICATION

It means that the stored procedure is executed only during replication.

 

 Example :

 

CREATE PROCEDURE ExamplePro

@result VARCHAR(50) OUT

AS

BEGIN

DECLARE @id INT;

SET @id = 8;

IF @id < 10

SET @result = 'This is Sample';

ELSE

SET @result = 'This also sample';

END;

 

Functions 

 Syntax:

 

CREATE FUNCTION [schema_name.]function_name

( [ @parameter [ AS ] [type_schema_name.] datatype

[ = default ] [ READONLY ]

, @parameter [ AS ] [type_schema_name.] datatype

[ = default ] [ READONLY ] ]

)

RETURNS return_datatype

[ WITH { ENCRYPTION

| SCHEMABINDING

| RETURNS NULL ON NULL INPUT

| CALLED ON NULL INPUT

| EXECUTE AS Clause ]

[ AS ]

BEGIN

[declaration_section]

executable_section

RETURN return_value

END;

 

Three types of user defined functions.

1.Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

2.Inline Table-Valued Function

User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

3.Multi-statement table-valued function

User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

 

 

 

 

 

 

Summary

In this blog, I tried to explain the difference between the stored procedure and the function. I hope after reading this blog, you will be able to understand the difference between the stored procedure and functions. The comments, criticism and suggestions are always welcome.