Answer Posted / ranjay

CREATE FUNCTION function_name
(@input_variables type)
RETURNS data_type of result returned by function
AS
BEGIN
..... SQL Statements
RETURN (data_value)
END

Let's demonstrate this statement by writing a function.
Oracle has a useful function called NEXT_DAY that computes
the date of the next named day of the week (i.e., Sunday,
Monday, etc.) after the given date. Go ahead and write your
own function for SQL Server (see Listing 1 for the code to
create a scalar function using NEXT_DAY).

Inline Table-Valued Functions
Now create a function that returns a table. Use the
Northwind database; it contains customers, orders, and order
details. Suppose you want to analyze your customers by
ranking them from best to worst. The function that you write
will return the customerid, total sales, and rank of each
customer in a table that can then be used by itself or
joined within other tables. Furthermore, you will pass a
parameter to the function to filter the customers based on
the rank (e.g., top 3, top 10, etc.).

First, create a view in the Northwind database that
summarizes the total sales for each customer, as follows:


use Northwind
go
if exists(select * from sysobjects where
name = 'vw_totalsales')
drop view vw_totalsales
go
create view vw_totalsales
as
(select CustomerID,sum (UnitPrice * Quantity *
1 - Discount) as 'totalsales'
from Orders o
inner join [Order Details] od
on (o.OrderID = od.OrderID)
group by Customerid )

To find the rank of each customer, you need to find the
number of distinct total sales values that are equal to or
less than the customer in question. See Listing 2 for the
code. Look more closely at the syntax you used. First of
all, in this function you stated "returns table." That tells
SQL Server that the function is returning a table of results
rather than a scalar value. But you didn't define the table
of results the function would be returning. Instead, you
followed it with a single return statement that has one huge
SELECT statement in it.

How is a full-text index updated?

998

---

What is a mutating table error and how can you get around it?

1117

---

What is a schema in ms sql server 2005?

1019

---

Which table keeps information about stored procedures?

979

---

What new data source types were added in ssrs 2014?

169

---

How can you fetch alternate records from a table?

1011

---

Explain boyce and codd normal form(bcnf)?

1008

---

Which operator do you use to return all of the rows from one query except rows are returned in a second query?

1037

---

How to generate create function script on an existing function?

1169

---

What are synonyms?

1008

---

How can you set the threshold at which sql server will generate keysets asynchronously?

1044

---

How to create a large table with random data for index testing in ms sql server?

1062

---

What is cube dimension? : sql server analysis services, ssas

1043

---

What are commonly used odbc functions in php?

1056

---

What is xdr?

1039

---