What is a function? Give some example?
Answers were Sorted based on User's Feedback
Answer / vikas kant
User Define Functions are used to define its own T-Sql
Function which can have 0 or more input parameters and
returns a single scalar data value type or table data type.
we have three type of UDF:-
1.Scalar user defined function
2.Inline table valued Function
3.Multiple table valued function
Is This Answer Correct ? | 6 Yes | 1 No |
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.
Is This Answer Correct ? | 3 Yes | 6 No |
Answer / dinesh sharma
In Sql Server Function are Like S.P Mean Batch of SQL
command. But Function Must Return Single Value
In SQL Server Four Type Of Function
1)Table-Valued Function:Return Datatable
2)Scaler Valued Function:return Single value
3)Aggreated Funtion:Return Aggreatged Result set
4)System Funtion:used by system like Rowset etc.
Is This Answer Correct ? | 8 Yes | 13 No |
How many types of triggers are there?
Define tool Manage Statistics in SQL Server 2000 query ?
What are ddl triggers and types of ddl trigger?
Why do we backup Active Directory ?
How can you transfer data from a text file to a database table? Or how can you export data from a table to a comma delimited (csv) file? Or how can you import data from ms access to a table in a database? Or how can you export data from a table to an excel file?
What are the disadvantages of primary key and foreign key in SQL?
What are the advantages of paper records?
One table Test with single column. These are the values in the table a b c d e f g h I need a query (without using any variable) with output as - a b c d e f g h
What is application role in sql server database security? : sql server security
What is a transact-sql statement?
i use few third party softwares. they r all having their own databases . but the data is repeated in all these databases - say a person is in all the three databases, but his name is stoared in diff format in all databases i want to create a centralised database ,and i dont want to re-enter the records . using the exisating records how can i build a centralised database?
What is read committed?