If Profiler catch the SP:Recompile event it means the stored procedure is recompiled. Then I tried all applicable SET options in stored procedure and run the SQL profiler by checking the SP:Recompile option, to know whether the SET option is causing recompilation or not. After knowing this fact a question raised in my mind that, If SET options causes recompilation then why we often use SET options in stored procedure. I was working on a database performance issue and found that SET options can cause stored procedure recompilation. Vision Cycles, Inc.Set Option causes Stored Procedure Recompilation INNER JOIN Purchasing.ProductVendor AS pv SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', The LEFT and CASE functions customize the appearance of the result set. It removes the EXECUTE AS CALLER clause and modifies the body of the procedure to return only those vendors that supply the specified product. The following example alters the uspVendorAllInfo stored procedure. ON v.BusinessEntityID = pv.BusinessEntityID SELECT v.Name AS Vendor, p.Name AS 'Product name', IF OBJECT_ID ( 'pVendorAllInfo', 'P' ) IS NOT NULLĭROP PROCEDURE pVendorAllInfo ĬREATE PROCEDURE pVendorAllInfo After this procedure is created, it is then modified to return a different result set. This procedure returns the names of all the vendors that supply Adventure Works Cycles, the products they supply, their credit ratings, and their availability. The following example creates the uspVendorAllInfo stored procedure. Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role. If a previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are enabled only if they are included in ALTER PROCEDURE.įor more information about stored procedures, see CREATE PROCEDURE (Transact-SQL). If the settings are different from those in effect when stored procedure was originally created, the behavior of the stored procedure may change. However, the current session settings for QUOTED_IDENTIFIER and ANSI_NULLS are included in the stored procedure when it is modified. Transact-SQL stored procedures cannot be modified to be CLR stored procedures and vice versa.ĪLTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers. To enable the option, use sp_configure.ĬLR procedures are not supported in a contained database. You can create, modify, and drop database objects that reference common language runtime modules however, you cannot execute these references in SQL Server until you enable the clr enabled option. The specified method must be a static method of the class.īy default, SQL Server cannot execute CLR code. ) to separate namespace parts, the class name must be delimited by using brackets ( ) or quotation marks ( ""). If the class has a namespace-qualified name uses a period (. class_name must be a valid SQL Server identifier and must exist as a class in the assembly. NET Framework assembly for a CLR stored procedure to reference. method_nameĪpplies to: SQL Server 2008 (10.0.x) and later. For more information, see the Best Practices, General Remarks, and Limitations and Restrictions sections in CREATE PROCEDURE (Transact-SQL).ĮXTERNAL NAME assembly_name. You can use the optional BEGIN and END keywords to enclose the statements. One or more Transact-SQL statements comprising the body of the procedure. Transact-SQL Syntax Conventions (Transact-SQL) Syntax - Syntax for SQL Server and Azure SQL DatabaseĪLTER Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement in SQL Server. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
0 Comments
Leave a Reply. |