Total Page Preview:   000000000985

What are characteristics and advantages of stored procedure?

Characteristics of Stored Procedured: 
  • SP can have any kind of DML and DDL statements. 
  • SP can have error handling (TRY ... CATCH).
  • SP can use all types of table.
  • SP can output multiple integer values using OUT parameters, but can return only one scalar INT value. ? SP can take any input except a table variable.
  • SP can set default inputs.
  • SP can use DSQL.
  • SP can have nested SPs.
  • SP cannot output 2D data (cannot return and output table variables).
  • SP cannot be called from a SELECT statement. It can be executed using only a EXEC/EXECUTE statement.

Advantages of Stored Procedured: 

  • Precompiled code hence faster.
  • They allow modular programming, which means it allows you to break down a big chunk of code into smaller pieces of codes. This way the code will be more readable and more easier to manage.
  • Reusability.
  • Can enhance security of your application. Users can be granted permission to execute SP without having to have direct permissions on the objects referenced in the procedure.
  • Can reduce network traffic. An operation of hundreds of lines of code can be performed through single statement that executes the code in procedure rather than by sending hundreds of lines of code over the network.
  • SPs are pre-compiled, which means it has to have an Execution Plan so every time it gets executed after creating a new Execution Plan, it will save up to 70% of execution time. Without it, the SPs are just like any regular TSQL statements.

 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments


                           
                           
                           
                           

Recent Posts

What is the difference between delete and truncate statements in SQL in English and Hindi
09-Apr-2023
 Explain difference between Union and Union all in SQL in English and Hindi
12-May-2023
 What is SQL group by function in English and Hindi
22-Apr-2023
 What is temporary table in SQL in English and Hindi
12-May-2023
 What is order by function in SQL in English and Hindi
23-Apr-2023
 What is cross join in SQL in English and Hindi
16-Apr-2023
 What is Having function in SQL in English and Hindi
07-May-2023
 What is concat function in SQL in English and Hindi
16-Apr-2023
 What is SQL select statement in English and Hindi
08-Apr-2023
 What is Auto increment in SQL in English and Hindi
18-Apr-2023
 What is count function in SQL in English and Hindi
16-Apr-2023
 What is replace function in SQL in English and Hindi
16-Apr-2023
 What is SQL delete statement in English and Hindi
14-Apr-2023
 What is drop statement in SQL in English and Hindi
18-Apr-2023
 What is SQL aliases in English and Hindi
13-Apr-2023
 How to convert date function and format in SQL in English and Hindi
14-Apr-2023
 What is SQL where clause in English and Hindi
11-Apr-2023
 What is SQL insert statement in English and Hindi
08-Apr-2023
 What is SQL update statement in English and Hindi
08-Apr-2023
 What is SQL Stored Procedures for SQL Server in English and Hindi
11-Apr-2023

Facebook User: