FOR REPLICATION
procedure has an object type RF in sys.objects and sys.procedures.SELECT DB_NAME() AS ThisDB;
returns the name of the current database.EXEC What_DB_is_this;
EXEC What_DB_is_that 2;
returns tempdb
.SELECT *
. Instead, specify the required column names. This can prevent some Database Engine errors that stop procedure execution. For example, a SELECT *
statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.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 |
View | Description |
---|---|
sys.sql_modules | Returns the definition of a Transact-SQL procedure. The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view. |
sys.assembly_modules | Returns information about a CLR procedure. |
sys.parameters | Returns information about the parameters that are defined in a procedure |
Returns the objects that are referenced by a procedure. |
Performance Monitor object name | Performance Monitor Counter name |
---|---|
SQLServer: Plan Cache Object | Cache Hit Ratio |
Cache Pages | |
Cache Object Counts* |
dbo.Departments
:Category | Featured syntax elements |
---|---|
Basic Syntax | CREATE PROCEDURE |
Passing parameters | @parameter • = default • OUTPUT • table-valued parameter type • CURSOR VARYING |
Modifying data by using a stored procedure | UPDATE |
Error Handling | TRY..CATCH |
Obfuscating the procedure definition | WITH ENCRYPTION |
Forcing the Procedure to Recompile | WITH RECOMPILE |
Setting the Security Context | EXECUTE AS |
uspGetEmployees
procedure can be executed in the following ways:GetPhotoFromDB
procedure that references the GetPhotoFromDB
method of the LargeObjectBinary
class in the HandlingLOBUsingCLR
assembly. Before the procedure is created, the HandlingLOBUsingCLR
assembly is registered in the local database.uspGetEmployees
procedure can be executed in the following ways:D
).uspGetEmployees2
procedure can be executed in many combinations. Only a few possible combinations are shown here.uspGetList
procedure. This procedures returns a list of products that have prices that do not exceed a specified amount. The example shows using multiple SELECT
statements and multiple OUTPUT
parameters. OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQL statement to access a value set during the procedure execution.uspGetList
to return a list of Adventure Works products (Bikes) that cost less than $700
. The OUTPUT
parameters @Cost
and @ComparePrices
are used with control-of-flow language to return a message in the Messages window.@ListPrice
= variable is used.Currency
table:@NewHours
and one output parameter @RowCount
. The @NewHours
parameter value is used in the UPDATE statement to update the column VacationHours
in the table HumanResources.Employee
. The @RowCount
output parameter is used to return the number of rows affected to a local variable. A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours
. When the employee is paid hourly (SalariedFlag
= 0), VacationHours
is set to the current number of hours plus the value specified in @NewHours
; otherwise, VacationHours
is set to the value specified in @NewHours
.HumanResources.uspEncryptThis
procedure.WITH ENCRYPTION
option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.sp_helptext
:The text for object 'HumanResources.uspEncryptThis' is encrypted.
sys.sql_modules
catalog view:WITH RECOMPILE
clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.CALLER
specifies that the procedure can be executed in the context of the user that calls it.TransactionScope
to run a transaction in an ASP.Net app. This transaction is supposed to save some data across multiple tables and then send an email to subscribers.TransactionScope
, when it includes a call to a stored procedure that has its own transaction in SQL Server 2014, or should I remove the SQL transaction statements i.e. begin tran
, commit tran
and rollback tran
statements from the stored procedure being called within this TransactionScope
?TransactionScope
:SaveEmailData
:TransactionScope
can still work when wrapping a TSQL BEGIN / COMMIT TRANSACTION
or an ADO SqlConnection.BeginTransaction
. When wrapping a single connection, the behaviour is similar to nesting transactions in Sql
:@@TranCount
will be incremented on each BEGIN TRAN
COMMIT TRAN
will simply decrement @@TRANCOUNT
. The transaction will only be committed if @@TRANCOUNT
hits zero.ROLLBACK TRAN
will abort the whole transaction (i.e. @@TRANCOUNT to zero), unless you are using Save Points (i.e. SAVE TRANSACTION xx
.. ROLLBACK TRANSACTION xx
.@@TRANCOUNT
differs when exiting the SPROC from the value it had when entering a SPROC.TransactionScope
and remove any manual BEGIN TRAN / COMMIT TRAN
logic from cluttering up your TSQL.ROLLBACK
in the BEGIN CATCH
block will abort the entire outer transaction and will likely cause further errors in the outer TransactionScope
as the @@TRANCOUNT
rule has not been adhered to. A nested transaction pattern such as this should be observed if a SPROC needs to operate in both a nested or standalone transaction fashion.TransactionScope
can easily escalate into a distributed transaction e.g. if you are using different connection strings or controlling other resources in under the transaction scope.create procedure
in EXEC('..')
, like this:M2
is the name of the mark.CandidateDelete
is marked.