Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Create an Multi-Statement Table-Valued Function?
Submitted by: AdministratorTo 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
<pre>
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
</pre>
Submitted by: Administrator
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
<pre>
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
</pre>
Submitted by: Administrator
<pre>SELECT * FROM dbo.Yearly_Stats(1900, 1905);
GO
year min max counts
----------- ----------- ----------- -----------
1900 -999932 996991 638
1901 -997138 991874 592
1902 -996779 997315 594
1903 -995476 998520 636
1904 -994838 998956 635
1905 -993178 996249 595
(6 row(s) affected)
</pre>
Submitted by: Administrator
GO
year min max counts
----------- ----------- ----------- -----------
1900 -999932 996991 638
1901 -997138 991874 592
1902 -996779 997315 594
1903 -995476 998520 636
1904 -994838 998956 635
1905 -993178 996249 595
(6 row(s) affected)
</pre>
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.