Programming in MS SQL Server 2012

Stored Procedure

A stored procedure in SQL Server 2012 is a collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method that stores in single database object. By default, SQL Server checks SQL query's syntax & semantic errors when execute any user request query in database. Yet, Stored Procedure is a group of Transact-SQL statements that compiled into a single query execution plan.
Advantage of Stored Procedures
01. Reduce server/client network traffic
By default, SQL Server checks SQL query's syntax and semantic errors when execute any user request query in database. Yet, Stored Procedure is a group of Transact-SQL statements that compiled into a single query execution plan. This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network.
02. Database Security
Many database users and client application program can access particular database objects through a stored procedure, even if the users and programs do not have direct permissions on those database objects. When calling a stored procedure over the network, only the call to execute the stored procedure is visible. Malicious users cannot see table and database objects or search for critical data.
Stored Procedure parameters are help to secure against SQL injection attacks. Since parameter input is treated like a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the procedure and compromise security.
03. Reuse of code
Stored Procedures contains Business Logic, any database users or Application can access the stored procedure to execute redundant database operation that avoid re-write Transact-SQL statements.
04. Easier Maintenance
Stored Procedures are store in Database Layer. Stored Procedure needs to update when Database Structure or Business Logic will update and no need to update Application.
05. Improved Performance
SQL Server compiles stored procedure, when first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.
Limitation of Stored Procedures
The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATE	CREATE SCHEMA	SET SHOWPLAN_TEXT
CREATE DEFAULT	CREATE or ALTER TRIGGER	SET SHOWPLAN_XML
CREATE or ALTER FUNCTION	CREATE or ALTER VIEW	USE database_name
CREATE or ALTER PROCEDURE	SET PARSEONLY	-
CREATE RULE	SET SHOWPLAN_ALL	-

A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved. Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure will fail at execution time if the referenced tables do not exist.
You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. However, you can pass a function as a variable as shown in the following example.

-- Sample Program --

-- Passing the function value as a variable.

DECLARE @CheckDate datetime = GETDATE();

EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;

If the procedure makes changes on a remote instance of SQL Server, the changes cannot be rolled back. Remote procedures do not take part in transactions.

* * * * *

Your Comment To AUTHOR