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
                       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>
                       As
                           (
                         ...............................................................
                         ..............................................................
                            )

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.


Sql Server Related:



Comments

Post a Comment