{"id":130,"date":"2014-04-22T20:25:00","date_gmt":"2014-04-22T19:25:00","guid":{"rendered":"https:\/\/debuggersspace.com\/index.php\/2014\/04\/22\/sql-server-stored-procedure\/"},"modified":"2014-04-22T20:25:00","modified_gmt":"2014-04-22T19:25:00","slug":"sql-server-stored-procedure","status":"publish","type":"post","link":"https:\/\/debuggersspace.com\/index.php\/2014\/04\/22\/sql-server-stored-procedure\/","title":{"rendered":"SQL Server Stored Procedure?"},"content":{"rendered":"
\n
\n \t<\/i> Read Time:<\/span>2 Minute, 26 Second <\/div>\n\n <\/div>
\nDefination<\/span><\/u><\/b>:<\/span><\/b><\/span><\/p>\n
\n
<\/span><\/span><\/div>\n
\n-In <\/span>database management systems (DBMSs), an operation that is stored with the database <\/span>server. Typically, stored procedures are written in <\/span>SQL. They’re especially important for <\/span>client-serverdatabase systems because storing the procedure on the server side means that it is available to all <\/span>clients.<\/span><\/span>
\n
<\/span><\/span>
\n-A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure<\/span><\/span><\/div>\n
\n
<\/span><\/div>\n
\nAdvantages:<\/span><\/span><\/u><\/b><\/div>\n
\n
<\/span><\/span>
\n <\/span><\/span>a) Stored procedure allows modular programming. <\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nYou can create the procedure once, store it in the database, and call it any number of times in your program. <\/span><\/span><\/div>\n
\n
<\/span><\/span>
\nb) Stored Procedure allows faster execution. <\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nIf the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times. <\/span><\/span><\/div>\n
\n
<\/span><\/span>
\nc) Stored Procedure can reduce network traffic. <\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nAn operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.<\/span><\/span><\/div>\n
\n
<\/span><\/span>
\nd) Stored procedures provide better security to your data<\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nUsers can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure’s statements directly.<\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nIn SQL we are having different types of stored procedures are there<\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\na)    <\/span><\/span>System Stored Procedures<\/span><\/span><\/span><\/div>\n
\nb)    <\/span><\/span>User Defined Stored procedures<\/span><\/span><\/span><\/div>\n
\nc)    <\/span><\/span>Extended Stored Procedures<\/span><\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nSystem Stored Procedures<\/span><\/u><\/b>:<\/span><\/b><\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nSystem stored procedures are stored in the master database and these are starts with a sp_ <\/b>prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables <\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nUser Defined Stored Procedures<\/span><\/u><\/b>:<\/span><\/b><\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nUser Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don\u2019t use sp_ <\/b>prefix <\/b>because <\/b>if we use the sp_ <\/b>prefix first it will check master database then it comes to user defined database<\/span><\/span><\/div>\n
\n
<\/span><\/span><\/div>\n
\nExtended Stored Procedures:<\/span><\/span><\/u><\/b><\/div>\n
\n
<\/span><\/span><\/div>\n
\nExtended stored procedures are the procedures that call functions from DLL files. Now a day\u2019s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures. <\/span><\/span>
\n
<\/span><\/span>
\nOverview of SQL Server Stored Procedure<\/a><\/span><\/span><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Defination: -In database management systems (DBMSs), an operation that is stored with the database server. Typically, stored procedures are written in SQL. They’re especially important for client-serverdatabase systems because storing the procedure on the server side means that it is available to all clients. -A stored procedure is a group of sql statements that has been created and stored in […]<\/p>\n","protected":false},"author":43,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","footnotes":""},"categories":[34,27],"tags":[],"class_list":["post-130","post","type-post","status-publish","format-standard","hentry","category-ibm","category-sql"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Himanshu Namdeo","author_link":"https:\/\/debuggersspace.com\/author\/admin\/"},"uagb_comment_info":0,"uagb_excerpt":"Defination: -In database management systems (DBMSs), an operation that is stored with the database server. Typically, stored procedures are written in SQL. They’re especially important for client-serverdatabase systems because storing the procedure on the server side means that it is available to all clients. -A stored procedure is a group of sql statements that has been created and stored in…","_links":{"self":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/130"}],"collection":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/comments?post=130"}],"version-history":[{"count":0,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/130\/revisions"}],"wp:attachment":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/media?parent=130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/categories?post=130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/tags?post=130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}