Return the number of rows of a sub query in a stored procedure 1

[Geee]

Hi folks,

I have the following query that returns the unique rows from a couple of tables...

SELECT DISTINCT GameOptionsMap.Value, GameOptionsMap.PositionID FROM playergamepositionmap
INNER JOIN GameOptionsMap ON (playergamepositionmap.GameID = GameOptionsMap.GameID)
WHERE playergamepositionmap.GameOutcome = 1 AND playergamepositionmap.PlayerID = 27
AND GameOptionsMap.GameOptionID = 2

What I want to do is return the number of rows returned by this query without returning any actual values to the output of the SP. Any ideas greatfully received!

G

-Geeeeeeeeeeeeeeeeeeeeeeee-

 

[bborissov]

ALTER PROCEDURE YourSPName
AS
BEGIN
DECLARE @Rows int
SET @Rows = 0 -- Just in case you didn't have any rows

SELECT @Rows = COUNT(*)
FROM (SELECT DISTINCT GameOptionsMap.Value,
                      GameOptionsMap.PositionID
      FROM playergamepositionmap
          INNER JOIN GameOptionsMap ON
                     (playergamepositionmap.GameID = GameOptionsMap.GameID)
          WHERE playergamepositionmap.GameOutcome =  1 AND
                playergamepositionmap.PlayerID    = 27 AND
                GameOptionsMap.GameOptionID       = 2) Tbl1
RETURN @Rows
END

NOT TESTED !!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.