Idola Blogs

CROSS APPLY

 

Here is another nice one (CROSS APPLY) and also note the key features here:

Create Function GetOrderDetails(@OrderId Int, @count Int)

      returns table

As

RETURN

      Select top(@count) * from [Order Details]

      Where OrderId = @OrderId

In the above example note the usage of @count in top. I just wanted to show the output of the following statement using the CROSS APPLY statement:

Select * from Orders

Inner join [Order Details] on Orders.OrderId = [order details].OrderId

Check this:

Select O.OrderId, OrderDate, ProductId, UnitPrice, Quantity from orders O

CROSS APPLY

      dbo.GetOrderDetails(O.OrderID, 3) AS D

ORDER BY

      O.OrderId Asc

 

This statement returns all the Orders and only the first three detail records. The APPLY clause acts like a JOIN without the ON clause. Refer to both CROSS APPLY and OUTER Apply here.

 

Event Notification and DDL Triggers

 

This function called Event Notifications is a feature that can be used by applications using SQL Server. This Event notifications executes in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.

A sample event will be like this:

 

CREATE EVENT NOTIFICATION log_ddl1

   ON SERVER

   FOR ALTER_TABLE

   TO SERVICE ‘//northwind.com/archiveservice’ , ‘current database’;

 

In the above example, ALTER_TABLE is a DDL statement. Refer to DDL Events for Use with Event Notifications for a complete list of DDL Events.

 

Similarly DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. The old DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views. DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.

USE Northwind;

GO

IF EXISTS (SELECT * FROM sys.triggers

    WHERE parent_class = 0 AND name = ‘safety’)

DROP TRIGGER safety

ON DATABASE;

GO

CREATE TRIGGER safety

ON DATABASE

FOR DROP_SYNONYM

AS

   RAISERROR (‘You must disable Trigger “safety” to drop synonyms!’,10, 1)

   ROLLBACK

GO

DROP TRIGGER safety

ON DATABASE;

GO

 

 

 

 

 

Error Handling

 Simply anything can be handled with the TRY/CATCH:

BEGIN TRY

Select 1/0

Select Cast(‘a’ as DateTime)

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER() as ErrorNumber,

        ERROR_MESSAGE() as ErrorMessage;

END CATCH;

 

 

These statements will show the following errors.

 

 

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() as ErrorState,

        ERROR_LINE () as ErrorLine,

        ERROR_PROCEDURE() as ErrorProcedure,

        ERROR_MESSAGE() as ErrorMessage;