Better Understanding

/*APPLY*/

/*Today I want to tell the one very good new feature of SQL 2005 -- APPLY (CROSS APPLY/OUTER APPLY)*/


/*We can use APPLY operator with table valued function. Before going to APPLY. I ll brief types of Functions*/

/*Two Main types of functions*/
--1.System Function
--2.User defined function

--1.System Functions

/*These Functions already defined in SQL server for various operations*/

/*We can split system functions to two types*/
--1.Scalar Functions
--2.Aggregate Functions

--1.Scalar Functions

/*It works only for single value/variable and returns single value*/
/*Ex:*/

SELECT ABS(-10) /*It will return absolute value*/

DECLARE @Name VARCHAR(10)
SET @Name = 'moov'
select UPPER(@Name) /*It will convert Lower case to Upper Case*/

/*Some more Scalar functions are : LTRIM(),RTRIM(),ROUND(),CONVERT(),....*/

--2.Aggregate Functions
/*It will accept multible rows of values and return one result*/

if object_id('tempdb..#temp') is not null
DROP table #temp
GO
create table #temp
(
sno int,
e_name varchar(100),
dept  varchar(100)
)
go
insert into #temp
select 1,'vicky','sw' union select 2,'moov','bill' union select 3,'anand','acc' union select 5,'AS','sw'

select max(sno) from #temp /*It will return maximum value from 3 rows*/

SELECT COUNT(sno) from #temp /*It will return count of table*/

/*Some more Aggregate functions are : MIN(),AVG(),....*/

--2.User defined function
/*This functions are defined by SQL developers*/

/*We can split User defined functions to two types*/

--1.Scalar Functions
--2.Table valued Functions

--1.Scalar Functions
/*Again it accepts single values return single value*/


IF OBJECT_ID('dbo.FN_ADD_NAME') IS NOT NULL
DROP FUNCTION dbo.FN_ADD_NAME
GO

CREATE FUNCTION FN_ADD_NAME
(
@Name1 VARCHAR(100), /*Inputs*/
@Name2 VARCHAR(100)
)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN (@Name1 + ' From ' + @Name2 + ' Team.')
END

/*We use the above function in select list of table*/
GO
SELECT sno,e_name,dept,dbo.FN_ADD_NAME(e_name,dept) as msg from #temp

/*Or we can use simple select list*/

SELECT dbo.FN_ADD_NAME('BS','Claims')

--2.Table valued Functions
/*It will return table values*/

IF OBJECT_ID('dbo.TEMP_SALARY') IS NOT NULL
DROP table dbo.TEMP_SALARY

go
CREATE TABLE TEMP_SALARY
(
SNO INT,
SALARY MONEY
)

INSERT INTO TEMP_SALARY
SELECT 1,'1000' UNION SELECT 2,'2000' UNION SELECT 3,'1500' UNION SELECT 4,'3000'


IF OBJECT_ID('dbo.FN_RETURN_SALARY') IS NOT NULL
DROP FUNCTION dbo.FN_RETURN_SALARY
go

CREATE FUNCTION FN_RETURN_SALARY
(
@sno INT, /*inputs*/
@AllValue INT
)
RETURNS @Salary TABLE
(
SNO INT,
SALARY MONEY
)
AS
BEGIN

INSERT INTO @Salary
SELECT * FROM TEMP_SALARY WHERE SNO =@sno OR 1=@AllValue /*Simple Trick to restrict output rows*/

RETURN
END

GO
/*We use the above function in simple select*/

SELECT * FROM FN_RETURN_SALARY(1,1) /*All values - 1  -- Return Sno 1*/

SELECT * FROM FN_RETURN_SALARY(4,2) /*Specific Value -- Return Sno 4*/

/*Now I want to get Salar with each name from these two tables using table function*/

SELECT * FROM #TEMP
SELECT * FROM TEMP_SALARY

/*You cant use inner join here. But you can use APPLY here- This is the new feature in SQL 2005*/

SELECT * FROM #TEMP
CROSS APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/


SELECT * FROM #TEMP
OUTER APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/

/*It will return NULL if function reurn nothing*/


/*So You can use APPLY operator with table valued function effectively. We can use this feature extensivly in our project*/

/*
Note:

/*BAS Team : You cant use this feature in our SQL 2005. Because this feature requires db compatability level should be 90. Our level is 80
So I created DB "KSCOPE" with db compatability 90 in dtdbtest server. You can use this feature in "KSCOPE" db. If you want to know about db compatability level
Please contact me.

/*If you want to see the error try to run this in dtdbtest..*/
*/

/*
DenRX Team : You can simply run this in your environment ;-)
*/

Thank You!
*/