STORED PROCEDURE AND FUNCTION

Stored Procedure:-

• Stored procedures return a zero or N value.
• The procedure can work with insert, update, delete and select statement.
• We can use functions inside the stored procedure.
• Using the exec command we call procedure.
• Return keyword used to exit the procedure.
• The procedure can have both input and output parameter.

Original Table

Example:-

Create procedure output

AS

Begin

UPDATE [dbo].[tbl]

SET [name]=’ANKITA’

WHERE id=1

End

Output:-

exec output

select * from [dbo].[tbl]

Updated Table

Function:

• The function can return a scalar value.
• Function only works with select statements.
• We can not use a stored procedure inside the function.
• Using the select command we call function.
• Return keyword to return the value.
• The function can have only input parameter.

Example:-

create function fun()

returns money

AS

Begin

RETURN(SELECT SUM([salary])

FROM [dbo].[tbl])

End

Output:-

USE [DB]

GO

SELECT [dbo].[fun]() as Total

GO

Result

If you are newer to database learning -SQL Server recommended is the following must-watch video: -