|
INTERVIEW QUESTIONS
DATABASE
MS SQL SERVER
DETAILS
Question: How To Create an Multi-Statement Table-Valued Function?
Answer: To create a multi-statement table-valued function, you need to define a temporary table as the returning table in the function. INSERT statements should be used to insert data into the returning table.
The tutorial exercise below shows you a simple example of how to build a temporary table and make it as the returning table of a function:
USE GlobalGuideLineDatabase; GO
CREATE FUNCTION Yearly_Stats(
@start_year INT, @end_year INT)
RETURNS @stats TABLE (year INT,
min INT, max INT, counts INT)
AS BEGIN
DECLARE @year INT;
SET @year = @start_year;
WHILE @year <= @end_year BEGIN
INSERT INTO @stats
SELECT @year AS year, MIN(counts) AS min,
MAX(counts) AS max, COUNT(*) AS counts
FROM fyi_links WHERE DATEPART(YEAR, created) = @year;
SET @year = @year + 1;
END;
RETURN;
END
GO
|
|
|
Category |
MS SQL Server Interview Questions & Answers -
Exam Mode /
Learning Mode
|
Rating |
(0.3) By 8601 users |
Added on |
9/24/2014 |
Views |
70748 |
Rate it! |
|
|
Question:
How To Create an Multi-Statement Table-Valued Function?
Answer:
To create a multi-statement table-valued function, you need to define a temporary table as the returning table in the function. INSERT statements should be used to insert data into the returning table.
The tutorial exercise below shows you a simple example of how to build a temporary table and make it as the returning table of a function:
USE GlobalGuideLineDatabase; GO
CREATE FUNCTION Yearly_Stats(
@start_year INT, @end_year INT)
RETURNS @stats TABLE (year INT,
min INT, max INT, counts INT)
AS BEGIN
DECLARE @year INT;
SET @year = @start_year;
WHILE @year <= @end_year BEGIN
INSERT INTO @stats
SELECT @year AS year, MIN(counts) AS min,
MAX(counts) AS max, COUNT(*) AS counts
FROM fyi_links WHERE DATEPART(YEAR, created) = @year;
SET @year = @year + 1;
END;
RETURN;
END
GO
Source: CoolInterview.com
If you have the better answer, then send it to us. We will display your answer after the approval.
Rules to Post Answers in CoolInterview.com:-
- There should not be any Spelling Mistakes.
- There should not be any Gramatical Errors.
- Answers must not contain any bad words.
- Answers should not be the repeat of same answer, already approved.
- Answer should be complete in itself.
|
|
Related Questions |
View Answer |
|
How To Create an Inline Table-Valued Function?
|
View Answer
|
|
How Many Ways to Create Table-Valued Functions?
|
View Answer
|
|
How Many Categories of Functions based Their Return Modes?
|
View Answer
|
|
How To Provide Default Values to Function Parameters?
|
View Answer
|
|
Can You Pass Expressions to Function Parameters?
|
View Answer
|
|
How To Provide Values to User Defined Function Parameters?
|
View Answer
|
|
How To Create User Defined Functions with Parameters?
|
View Answer
|
|
How To Modify an Existing User Defined Function?
|
View Answer
|
|
How To Generate CREATE FUNCTION Script on an Existing Function?
|
View Answer
|
|
How To Drop an Existing User Defined Function in MS SQL Server?
|
View Answer
|
|
How To List All User Defined Functions in the Current Database?
|
View Answer
|
|
How To Use User Defined Functions in Expressions?
|
View Answer
|
|
How To Create a Simple User Defined Function in MS SQL Server?
|
View Answer
|
|
What Are User Defined Functions in MS SQL Server?
|
View Answer
|
|
Can Another User Execute Your Local Temporary Stored Procedures?
|
View Answer
|
|
How To Create a Local Temporary Stored Procedure?
|
View Answer
|
|
How To Receive Output Values from Stored Procedures?
|
View Answer
|
|
How To Define Output Parameters in Stored Procedures?
|
View Answer
|
|
How To Provide Default Values to Stored Procedure Parameters?
|
View Answer
|
|
Can You Pass Expressions to Stored Procedure Parameters?
|
View Answer
|
Please Note: We keep on updating better answers to this site. In case you are looking for Jobs, Pls Click Here Vyoms.com - Best Freshers & Experienced Jobs Website.
View All MS SQL Server Interview Questions & Answers - Exam Mode /
Learning Mode
|