• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1429
  • Last Modified:

SQL Server function that returns multiple rows

Hello,

In SQL Server 2000, is there a way to set up functions that return multiple values?

For example, if table SALES_2007 has two columns:  MONTH (01, 02, 03, 04, etc.) and SALES, and I'd like the function to take two parameters of type 2-digit month and return a list of values for all months between the two months entered.

So if someone entered FUNCTION('02, '04'), the function would return 3 rows, one for each month and the sales associated with each month.

Thanks
0
hpsuser
Asked:
hpsuser
  • 6
  • 3
  • 2
  • +1
3 Solutions
 
appariCommented:
try this

Create FUNCTION [dbo].[test]
(      
@startMon varchar(2),
@endtMon  varchar(2)
)
RETURNS @month TABLE(monthNo  varchar(2))
AS
begin
      
      declare @intMon int
      declare @intStMon int
      declare @intEndMon int

      Select @intStMon=convert(int,@startMon)
      Select @intEndMon=convert(int,@endtMon)
      Select @intMon=@intStMon

      while @intMon<=@intEndMon
      begin
            insert into @month select right('00' + convert(varchar(2),@intMon),2)
            Select @intMon = @intMon+1
      end
      return
end

execute it like this
select * from dbo.test('03','05')
0
 
David ToddSenior DBACommented:
Hi appari,

Why the loop?

So long as the months are 01, 02, ... between should

Create FUNCTION [dbo].[test]
(      
@startMon varchar(2),
@endMon  varchar(2)
)
RETURNS @month TABLE(monthNo  varchar(2), Sales money )
AS
begin
            insert into @month
            select s.Month, s.Sales
            from dbo.sales_2007 s
            where s.month bween @startMon and @endMon
      return
end

Regards
  David
0
 
appariCommented:
i just tried not to use the table sales_2007 inside the function.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Scott PletcherSenior DBACommented:
I don't think it's quite that simple ... what if the range is 12-->03?  :-)
0
 
Scott PletcherSenior DBACommented:
Please try this:

ALTER FUNCTION dbo.GetMonths (
    @startMonth TINYINT,
    @endMonth TINYINT
)
RETURNS @months TABLE (
    [month] CHAR(2)
    )
AS
BEGIN
IF @startMonth > 12 OR @endMonth > 12
BEGIN
    INSERT INTO @months VALUES('Er')
    RETURN
END --IF
IF @startMonth > @endMonth
BEGIN
    WHILE @startMonth <= 12
    BEGIN
        INSERT INTO @months VALUES(RIGHT('0' + CAST(@startMonth AS VARCHAR(2)), 2))
        SET @startMonth = @startMonth + 1
    END --WHILE
    SET @startMonth = 1
END --IF
WHILE @startMonth <= @endMonth
BEGIN
    INSERT INTO @months VALUES(RIGHT('0' + CAST(@startMonth AS VARCHAR(2)), 2))
    SET @startMonth = @startMonth + 1
END --WHILE
RETURN
END --FUNCTION
0
 
Scott PletcherSenior DBACommented:
To avoid looping, you could create a table of sequential numbers -- which is actually remarkably useful for a lot of things in SQL.  However, the processing for at most 12 numbers is limited enough that you shouldn't notice the looping unless the function is called a *lot*.

Btw, I would definitely *not* use the sales table itself to generate the results -- not least because it seems to change every year, but also because a month might be missing.
0
 
Scott PletcherSenior DBACommented:
D'OH, SORRY, broke my own rule and relied on other posts instead of reading the original q.

Will work a query that includes the monthly sales asap.
0
 
Scott PletcherSenior DBACommented:
ALTER FUNCTION dbo.GetMonths (
    @startMonth TINYINT,
    @endMonth TINYINT
)
RETURNS @months TABLE (
    [month] CHAR(2),
    [sales] DECIMAL(9),
    UNIQUE CLUSTERED ([month])
    )
AS
BEGIN
IF @startMonth > 12 OR @endMonth > 12
BEGIN
    INSERT INTO @months VALUES('Er', -1)
    RETURN
END --IF
IF @startMonth > @endMonth
BEGIN
    WHILE @startMonth <= 12
    BEGIN
        INSERT INTO @months VALUES(RIGHT('0' + CAST(@startMonth AS VARCHAR(2)), 2), 0)
        SET @startMonth = @startMonth + 1
    END --WHILE
    SET @startMonth = 1
END --IF
WHILE @startMonth <= @endMonth
BEGIN
    INSERT INTO @months VALUES(RIGHT('0' + CAST(@startMonth AS VARCHAR(2)), 2), 0)
    SET @startMonth = @startMonth + 1
END --WHILE
UPDATE mth
SET sales = sls.sales
FROM @months mth
LEFT OUTER JOIN (
    SELECT [month], SUM(sales) AS sales
    FROM sales_2007
    WHERE [month] IN (SELECT [month] FROM @months)
    GROUP BY [month]
) AS sls ON sls.[month] = mth.[month]
RETURN
END --FUNCTION
0
 
hpsuserAuthor Commented:
Thanks for all the answers.  Before I can get the multiple rows to return, I'm having trouble early on getting a TABLE type to return from the function?  Here's what my code is:

drop FUNCTION dbo.test
go
Create FUNCTION dbo.test
(      
@startMon varchar(2),
@endMon  varchar(2)
)
returns table(varchar(2), int)
AS
begin
declare @monthtbl table(monthno varchar(2), sales int)
insert into @monthtbl (monthno,sales) values('01',100)
return @monthtbl
end
go
0
 
appariCommented:
Create FUNCTION dbo.test
(      
@startMon varchar(2),
@endMon  varchar(2)
)
returns @monthtbl table(monthno varchar(2), sales int)
--returns table(varchar(2), int)
AS
begin
--declare @monthtbl table(monthno varchar(2), sales int)
insert into @monthtbl (monthno,sales) values('01',100)
return --@monthtbl
end
go
0
 
Scott PletcherSenior DBACommented:
You must make the table name a variable (that is, preceded by @);
you must use a column name as well as a data type.

See function I posted earlier for sample syntax.
0
 
hpsuserAuthor Commented:
thanks everyone, I got it working.  Thiss will definitely come in handy.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now