SQL Syntax
1. Stored procedure
Basic structure:
ALTER PROCEDURE [Procedure name]
AS
SET NOCOUNT ON
BEGIN TRANSACTION
PRINT "Transaction complete"
COMMIT TRANSACTION
Example:
ALTER PROCEDURE Examples.Worker_AddWithAssignment @WorkerName nvarchar(50), @CompanyName nvarchar(50) AS
SET NOCOUNT ON;
DECLARE @NewWorkerId int; IF @WorkerName IS NULL or @CompanyName IS NULL THROW 50000,'Both parameters must be not null',1;
BEGIN TRANSACTION
INSERT INTO Examples.Worker(WorkerName) VALUES (@WorkerName); IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION; THROW 50000,'Error occurred inserting data into
Examples.Worker table',1; END; SELECT @NewWorkerId = SCOPE_IDENTITY()
INSERT INTO Examples.WorkerAssignment(WorkerId, CompanyName) VALUES (@NewWorkerId, @CompanyName); IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION; THROW 50000, 'Error occurred inserting data into Examples.WorkerAssignment table',1; END; COMMIT TRANSACTION;
2. Function
CREATE FUNCTION Examples.ReturnIntValue ( @Value int )
RETURNS int AS BEGIN RETURN @Value END;
Example:
CREATE FUNCTION Sales.Customers_ReturnOrderCount ( @CustomerID int, @OrderDate date = NULL)
RETURNS INT WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING AS BEGIN DECLARE @OutputValue int SELECT @OutputValue = COUNT(*) FROM Sales.Orders WHERE CustomerID = @CustomerID AND (OrderDate = @OrderDate OR @OrderDate IS NULL); RETURN @OutputValue END;
3. Trigger
Basic structure:
ALTER TRIGGER [Trigger name]
ON [Table that fires trigger]
AFTER INSERT [or other triggers]
AS
BEGIN
SET NOCOUNT ON
[Trigger content]
END
Example:
CREATE TRIGGER Examples.AccountContact_TriggerAfterDelete
ON Examples.AccountContact
AFTER DELETE AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0; BEGIN TRY IF EXISTS ( SELECT AccountId FROM Examples.AccountContact WHERE EXISTS (SELECT * FROM deleted WHERE deleted.AccountId = AccountContact.AccountId) GROUP BY AccountId HAVING SUM(CASE WHEN
PrimaryContactFlag = 1 then 1 ELSE 0 END) > 1) THROW 50000, 'One or more Accounts did not have one
primary contact.', 1; END TRY
BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; THROW; END CATCH;
END;
4. Extended Events
Basic structure:
CREATE EVENT SESSION [Capture Estimated Plans] ON SERVER
ADD EVENT sqlserver.query_pre_execution_showplan
ADD TARGET package0.event_file(SET filename=N'C:\temp\CaptureEstPlan.xel'),
[WITH (MAX_MEMORY=4MB, MAX_EVENT_SIZE=4MB)];
Alter EVENT SESSION [Capture Estimated Plans] ON SERVER
STATE = START;
Example:
CREATE EVENT SESSION [stored_proc] ON SERVER ADD EVENT sqlserver.sp_statement_completed( ACTION (sqlserver.session_id, sqlserver.sql_text)) ADD TARGET package0.event_file(
SET filename=N'C:\ExamBook762Ch4\query.xel',max_file_size (5),max_rollover_files=(4)), ADD TARGET package0.ring_buffer;
Example 1:
Answer 1:
Example 2:
Answer 2:
