Different Types of SQL Server Stored Procedures

SQL Server Stored Procedures and Security Factor:

Stored Procedures is a group of SQL statements with an assigned name that's stored in the database.

Security with Stored procedures:

Sql Stored procedures provide better security to your data. Users can have permission to execute a stored procedure if they do not have permission then they do not execute the procedure's statements directly.

Different types of stored procedures in sql server:

SQL server having different types of stored procedures, these are:-

System Stored Procedures

User Defined Stored procedures

1. Using input parameters
2. Using output parameters
3. Using Try Catch

Extended Stored Procedures

System Stored Procedures:

A system stored procedures of sql are stored in the master database and these are starts with a sp_ prefix.
This type of sql procedures can be used to perform variety of tasks to support sql server functions for external application in the system tables.
For check use this syntax of query.

sp_helptext <Sql-StoredProcedure_Name>

Some System procedures are Listed here:-

sp_rename : It is used to rename an database object like stored procedure,views,table etc.

sp_changeowner: It is used to change the owner of an database object.

sp_help: It provides details on any database object.

sp_helpdb: It provides the details of the databases defined in the Sql Server.

sp_helptext: It provides the text of a stored procedure reside in Sql Server

sp_depends: It provides the details of all database objects that depend on the specific database object

User Defined Stored Procedures:

A User Defined stored procedures are usually stored in user database and use to complete the tasks in the user database.
When the user Make these procedures don’t use sp_ prefix.
When we use the sp_ prefix then it will check master database first then it comes to user defined database.
Syntax of sql query for creating procedure.

CREATE PROCEDURE <procedure name>

(

...............................................................

..............................................................

)

Extended Stored Procedures:

An Extended stored procedures that are use to call functions from DLL files.
The extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.