Types of store procedure
Course- SQL Sever Store Procedure >
Three types are Sql store Procedure:
-
User Defined Stored procedures
-
System Stored Procedures
-
Extended Stored Procedures
User Defined Stored procedures:
The user defined stored procedures are created by users and stored in the current database.
A user-defined stored procedure is created in a user database and typically interacts with database objects. When you invoke a user-defined stored procedure, you specify the EXEC (or EXECUTE) command and the stored procedure’s schema-qualified name, and ments:
EXEC dbo.usp_Proc1 <arguments>;
While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database
System Stored Procedures:
These types stored procedure are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.
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 provide 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 provide the details of all database objects that depends on the specific database object.
Extended Stored Procedures
These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_
Example Below statements are used to log an event in the NT event log of the server without raising any error on the client application.
- declare @logmsg varchar(50)
- set @logmsg = suser_sname() + ': Tried to access the dotnet programe.'
- exec xp_logevent 5000, @logmsg
- print @logmsg
Example The below procedure will display details about the BUILTIN\Administrators Windows group.
EXEC xp_logininfo 'BUILTIN\Administrators'
Other Types of Stored Procedures
Temporary stored procedures:
You can create temporary procedures by prefixing their names with a single number symbol or a double one (# or ##). A single number symbol
wo would make the procedure a local temporary procedure, and two number symbols would make it a global one.