Define stored procedures?
Stored procedure is one of the important database objects. Stored procedure contains programming structure, it stores and executes on database server itself.
A stored procedure is a precompiled unit of Transact-SQL statements.
By using stored procedure
We could modify the data in database.
We could return scalar values and Entire result sets.
What are the Advantages of stored procedures?
• We could isolate database code in one stored procedure instead of writing hard-coded SQL statements throughout an application
• Reduces the network traffic
• Increases the security
• It will increase the performance
Explanation: When we executed stored procedure, it compiles in to query catch and creates query plan as well as an execution plan. Stored procedure will reuse this query plan and execution plan on next time execution onwards.
Explain Recompile option in stored procedure?
Stored procedure creates query plan and execution plan on first time execution and it will reuse the same query plan and execution plan on further stored procedure executions. Recompile option forces SQL Server to discard the query plan each time stored procedure executes and create a new plan.
You could execute stored procedure in following way
EXEC StoredProcedureName ‘Parametes’ WITH RECOMPILE
What are the conditions under which a stored procedure is recompiled?
• An index on a referenced table is dropped.
• The table is altered using ALTER TABLE.
• A rule or default is bound to the table or column.
• The stored procedure has been flagged for recompilation by executing sp_recompile on any referenced table.
• The stored procedure is executed using the WITH RECOMPILE option.
• The stored procedure is created using the WITH RECOMPILE option.
• All copies of the execution plan in cache are currently in use.
If you would like to view all SQL Server related topics click below link
SQL Server related topics
Great information.