How can i build array or table in a function and return it in SQL Server 2008

Question

I am using SQL Server 2008 and making a function that selects some records
i need to filter these records and return some records

CREATE  PROCEDURE getPlayerLeft  @minDate datetime ,@maxDate datetime ,@minLevel integer, @MaxLevel integer 
AS
declare @acc_id integer
declare @lv integer
DECLARE db_cursor CURSOR FOR  
SELECT DISTINCT account_id, MAX(lv) AS MAXLV
FROM Character
WHERE (logout_time BETWEEN @minDate AND @maxDate) AND (lv BETWEEN @minLevel AND @MaxLevel)
GROUP BY account_id      

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @acc_id,@lv   
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT Character.account, Character.race, Character.job, Character.job_1, Account.email
FROM Character, Account
WHERE (Character.account_id = @acc_id) AND (Character.lv = @lv) AND (Account.account_id = @acc_id)

//here i need to filter these selected record in some condition
//and save them in some collection (if existed) and return it    

FETCH NEXT FROM db_cursor
INTO @acc_id,@lv
END 

CLOSE db_cursor
DEALLOCATE db_cursor

Thanks a lot!

SQL <> fucntional. Think set based. You don't need a cursor at first glance — gbn
– gbn, Commented Nov 22, 2010 at 15:17

Anthony Faull · Accepted Answer · 2010-11-22 16:07:11Z

1

Write an Inline Function that uses a Common Table Expression or subquery.

CREATE FUNCTION getPlayerLeft (@minDate datetime, @maxDate datetime, 
                               @minLevel int, @MaxLevel int)
RETURNS TABLE AS RETURN

WITH maxlevel AS
(
    SELECT account_id, MAX(lv) AS lv
    FROM dbo.Character
    WHERE (logout_time BETWEEN @minDate AND @maxDate) 
        AND (lv BETWEEN @minLevel AND @MaxLevel)
    GROUP BY account_id
)
SELECT Character.account, Character.race, Character.job, 
       Character.job_1, Account.email
FROM dbo.Character
INNER JOIN dbo.Account ON Account.account_id = account_id
INNER JOIN maxlevel ON maxlevel.account_id = Character.account_id
                       AND maxlevel.lv = Character.lv

GO

SELECT account, race, job, job_1, email
FROM dbo.getPlayerLeft('2010-01-01', '2010-12-31', 1, 5)

answered Nov 22, 2010 at 16:07

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gbn · Accepted Answer · 2010-11-22 15:22:00Z

Your stored proc could look like this. No need for a CURSOR

Note the proper use of JOIN too.

SELECT
   Filtered.*
FROM
    (
    SELECT DISTINCT
          account_id, MAX(lv) AS MAXLV
    FROM
         Character
    WHERE
         (logout_time BETWEEN @minDate AND @maxDate) AND
         (lv BETWEEN @minLevel AND @MaxLevel)
    GROUP BY account_id
    ) FilterOne
    JOIN
    (
    SELECT
        Character.account_id, Character.lv, 
        Character.account, Character.race, Character.job, Character.job_1,
        Account.email
    FROM
        Character
        JOIN
        Account ON Character.account_id = Account.account_id
    ) Filtered
           ON FilterOne.account_id = Filtered.account_id AND FilterOne.MAXLV = Filtered.lv

WHERE
   --add condition here?

John Hartsock · Accepted Answer · 2010-11-22 14:36:14Z

0

SELECT * INTO #TempTable from [YOUR Query]

This will insert your records in a temp table then the last statement of your Procedure should be

SELECT * FROM #TempTable

answered Nov 22, 2010 at 14:36

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Comments

Donnie · Accepted Answer · 2010-11-22 14:37:14Z

0

You can return table variables from udfs. That link also has an example of a udf which returns one, scroll down to "An Example: Split".

You could also use temporary tables, but make sure you create the temp table before you call your sproc as exiting a sproc causes temp tables created in it to be dropped.

answered Nov 22, 2010 at 14:37

Donnie

47k10 gold badges67 silver badges88 bronze badges

Collectives™ on Stack Overflow

How can i build array or table in a function and return it in SQL Server 2008

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related