DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to join table & table-valued function with CROSS APPLY?

The following SQL Server T-SQL example scripts demonstrate how CROSS APPLY can be used to "join" a table and a table-valued function:

USE AdventureWorks2008;
-- sys.dm_exec_sql_text is a table-valued function (TFV) defined in master db - DMF

-- sys.dm_exec_query_stats is a Dynamic Management View - DMV

-- CROSS APPLY invokes the TVF for each row returned from the view SELECT query

SELECT DBID       = st.dbid,

       Query      = LEFT(st.text, 30),    

       ExecCount  = execution_count,

       WorkerTime = total_worker_time,

       PhysReads  = total_physical_reads,

       LogiReads  = total_logical_reads

FROM sys.dm_exec_query_stats qs

   CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

ORDER BY ExecCount desc;

/* DBID     Query                         ExecCount   WorkerTime  PhysReads

....

4     CREATE PROCEDURE sp_jobhistory      4           1000        0

4     CREATE PROCEDURE sp_jobhistory      4           0           1  ......*/

------------

USE AdventureWorks;

GO

-- SQL Server user-defined function - UDF - Table-valued function - inline TVF

CREATE FUNCTION Sales.fnTopNOrders

               (@CustomerID AS INT,

                @n          AS INT)

RETURNS TABLE

AS

  RETURN

    SELECT   TOP ( @n ) SalesOrderID,

                        ShipDate = convert(CHAR(10),ShipDate,112),

                        TotalDue = convert(VARCHAR,TotalDue,1)

    FROM     AdventureWorks.Sales.SalesOrderHeader

    WHERE    CustomerID = @CustomerID

    ORDER BY TotalDue DESC

GO

 

-- SQL join table-valued function - SQL use function to join table

-- SQL cross apply - SQL join table & table-valued function

SELECT   StoreName = s.Name,

         [Top].ShipDate,

         [Top].SalesOrderID,

-- SQL currency format – format money

         TotalDue = '$' + [Top].TotalDue

FROM     AdventureWorks.Sales.Store AS s

         INNER JOIN AdventureWorks.Sales.Customer AS c

           ON s.CustomerID = c.CustomerID

         CROSS APPLY AdventureWorks.Sales.fnTopNOrders(c.CustomerID,5) AS [Top]

WHERE    CustomerType = 'S'

ORDER BY StoreName,

         convert(MONEY,TotalDue) DESC

 

GO

/* Partial results

 

StoreName               ShipDate    SalesOrderID      TotalDue

A Bike Store            20020208    45283             $37,643.14

A Bike Store            20020508    46042             $34,722.99

A Bike Store            20011108    44501             $26,128.87

A Bike Store            20010808    43860             $14,603.74

A Great Bicycle Company 20010908    44125             $3,450.98

A Great Bicycle Company 20020308    45569             $2,828.58

*/
------------

-- SQL Server DMV with CROSS APPLY - sys.dm_exec_cached_plans Dynamic Management View
-- Get cached plans information - sys.dm_exec_sql_text Dynamic Management Function
SELECT  cp.*,
        sqltext.text
FROM  sys.dm_exec_cached_plans cp
  CROSS APPLY  sys.dm_exec_sql_text (cp.plan_handle) AS sqltext

------------

Related Link:

   All the Fuss about CROSS APPLY

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE