Types of Stored Procedures in SqlServer

Stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server. stored Procedures is that they are executed on the server side, to reduce the network traffic.

Types of Stored Procedures

1. System Defined Stored Procedure

These stored procedures are already defined in Sql Server. These are physically stored in hidden Sql Server. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures.  Its manage SQL Server through administrative tasks. These are in master and msdb database. Here is a list of some useful system defined procedure.

System Procedure	Description
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.

2. Extended Procedure

These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_ Extended procedures provide an interface to external programs for various maintenance activities. These are stored in Master database

Ex. EXEC xp_logininfo 'BUILTIN\Administrators'

3. User Defined Stored Procedure

These procedures are created by user for own actions and stored in the current database

4. CLR Stored Procedure

CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages

5. Temporary Stored procedures

The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection terminates between client and server

6. Remote Stored Procedures

The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission

     7. Dynamic Stored Procedures

Sp_executesql can be used to call any statement or batch, not just a stored procedure. Executing SQL statements that are built "on the fly" is referred to asdynamic SQL. You can use either sp_executesql or the EXECUTE command to execute your dynamic SQL statements.

DECLARE @string NVARCHAR(100)  
SELECT @string = 'select * from authors'  
EXEC sp_executesql @string --or EXEC(@string)

Async call

$.ajax({
 type: 'POST',
 url: "webMethods.asmx/fngetuser",
 data: "{'userid':'" + userid + "'}",
 contentType: 'application/json; charset=utf-8',
 dataType: 'json',
 async: true,
 success: function (msg) {
       $('[id$="Label1"]').html(msg.d[0]);
       $('[id*=ur_]').removeAttr('checked');
 },
 error: function (msg, a, b) { alert('swe'); }
 });


 $.getJSON("@Url.Action("GetLat", "Location")", { addr: varaddr },
 function (msg) {
 if (msg !== null && msg.Lat != 0) { }
 else { alert('Invalid address'); }
 });

Variance in Delegates

When you assign a method to a delegate, covariance and contravariance provide flexibility for matching with method signature.

Covariance permits a method to have return type as more derived than that defined in the delegate.

Contravariance permits a method that has parameter types that are less derived than those in the delegate type.