Total Page Preview:   000000000817

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

How to get the alternate rows or records from table in sql server
07-Jun-2017
 Data Warehouse Three Tier Architecture
20-Aug-2017
 SQL Server - Fragmentation on azure sql database
07-Oct-2018
 Limitations of Azure SQL Database
19-Sep-2019
 PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
 SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
 SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
 Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
 How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
 What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
 Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
 Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
 What is a Trigger and type of trigger in SQL server?
10-Sep-2021
 How many types of dimension tables available in data warehouse?
16-Sep-2021
 Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
 What are the differences between OLTP and OLAP?
07-Sep-2021
 What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
 What are the different types of Error Handling in SQL Server?
11-Sep-2021
 Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
 What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: