The use of DML is not limited to DML queries.
Usually, database developers create stored procedures that encapsulate
DML statements. These stored procedures can accept parameters. The
stored procedure’s behavior will vary depending on the parameter values
passed to it. Stored procedures can return multiple result sets and
messages. Often it is useful to include information about what data was
affected by the DML statement.
We will
examine the aspects of creating and executing DML. As this functionality
is key to creating SQL Server applications, expect to be asked about
the OUTPUT
clause and nonlogged DML operations in the exam. This article assumes
that you are familiar with writing DML statements as well as creating stored procedures and user-defined
functions .
As a best practice, ensure
that the performance of statements and stored procedures containing DML
is adequate. DML carries the overhead of data locking. Sometimes DML
statements are called simultaneously by multiple users, and could affect
large result sets. This could result in poor performance, blocking, or
even deadlocks. When you are using DML statements, always try to access
data in the same order, to avoid locking and blocking issues.
Let’s take a look at an
example of a stored procedure containing DML. This stored procedure will
accept a product ID and a price as parameters. It will then update the
price for the product with the given product ID. But how do we know what
product was affected? What was the old price? The OUTPUT clause serves this purpose. It returns data affected by the DML statement. View the execution results produced by Examples 8.1 and 8.2 to see what the OUTPUT statement can do.
Example 8.1. sp_UpdateProductPrice DML Stored Procedure
CREATE PROCEDURE sp_UpdateProductPrice(@ProductID int, @ListPrice money)
AS
UPDATE Production.Product SET ListPrice = @ListPrice
OUTPUT inserted.ProductID as ProductIDAffected, inserted.Name as
ProductNameAffected, deleted.ListPrice as OldPrice, inserted.ListPrice
as NewPrice
WHERE ProductID = @ProductID
|
Example 8.2. sp_UpdateProductPrice Execution Results
EXECUTE sp_UpdateProductPrice 316, 40
--Results:
--ProductIDAffected ProductNameAffected OldPrice NewPrice
------------------- ------------------- -------- --------
--316 Blade 00.00 40.00
--(1 row(s) affected)
|
As
you can see, this stored procedure not only executes the DML statement
and updates the data but also returns information about the data
affected using the OUTPUT
statement. We will examine how data returned by DML
statement execution can be passed back to the caller. This includes the
use of the OUTPUT statement, the NEWID function, the @@IDENTITY variable, and others. Finally, we will examine nonlogged DML operations, like the TRUNCATE TABLE
statement.
