Iterate through values in column

Transact-SQL
1

The following stored procedure works well as far as it goes but I need help to amend it.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
ALTER PROCEDURE [dbo].[calcDrawAE]
	-- Add the parameters for the stored procedure here



	@AE decimal (18,2) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare	@odds decimal (18,2)
	(SELECT @odds= sum
       (1/ (RacingMaster.RM_SP) )
		FROM    RacingMaster
		where RM_Draw='1')
declare	@totwins decimal (18,2)
(select @totwins =count(*) from RacingMaster
		where RM_Draw=1
        and Finishing_Position=1)
declare	@totRns decimal (18,2)
(select @totRns =count(*) from RacingMaster
		where RM_Draw ='1')

		set @AE=@totwins/@odds
return @AE
  
  end

The column RM_Draw holds integer values from 1 through to 20. Can someone please provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.

Many thanks.

2

Why not look to return the data as a set? Then you can then split it out in your application where needed.

As you haven't provided any DDL and sample data of the underlying tables, I've given a possible example of what you could do.

DECLARE @RacingMaster TABLE
(
	Race CHAR(1) NOT NULL,
	RM_Draw INT NOT NULL,
	Finishing_Position INT NULL,
	RM_SP DECIMAL(18,5) NULL
);

INSERT INTO @RacingMaster (Race,RM_Draw,Finishing_Position,RM_SP)
VALUES	('A',1,NULL,NULL),
		('A',2,NULL,NULL),
		('A',3,NULL,NULL),
		('A',4,NULL,NULL),
		('A',5,NULL,NULL),
		('A',6,NULL,NULL),
		('A',7,NULL,NULL),
		('A',8,NULL,NULL),
		('A',9,1,25),
		('A',10,NULL,NULL),
		('A',11,NULL,NULL),
		('A',12,NULL,NULL),
		('A',13,NULL,NULL),
		('A',14,NULL,NULL),
		('A',15,NULL,NULL),
		('A',16,NULL,NULL),
		('A',17,NULL,NULL),
		('A',18,NULL,NULL),
		('A',19,NULL,NULL),
		('A',20,NULL,NULL),
		('B',1,NULL,NULL),
		('B',2,NULL,NULL),
		('B',3,NULL,NULL),
		('B',4,NULL,NULL),
		('B',5,NULL,NULL),
		('B',6,NULL,NULL),
		('B',7,NULL,NULL),
		('B',8,NULL,NULL),
		('B',9,NULL,NULL),
		('B',10,NULL,NULL),
		('B',11,NULL,NULL),
		('B',12,1,50),
		('B',13,NULL,NULL),
		('B',14,NULL,NULL),
		('B',15,NULL,NULL),
		('B',16,NULL,NULL),
		('B',17,NULL,NULL),
		('B',18,NULL,NULL),
		('B',19,NULL,NULL),
		('B',20,NULL,NULL);

WITH Base (RM_Draw,TotalWins)
AS
(
	SELECT	RM1.RM_Draw,
			COUNT(*)
	FROM	@RacingMaster AS RM1
	WHERE	RM1.Finishing_Position = 1
	GROUP	BY RM1.RM_Draw
)

SELECT	RM.RM_Draw,
		AE = B.TotalWins / SUM(1.0/(RM.RM_SP))
FROM	@RacingMaster AS RM
LEFT
JOIN	Base AS B
		ON	RM.RM_Draw = B.RM_Draw
GROUP	BY RM.RM_Draw,
		B.TotalWins
ORDER	BY RM.RM_Draw ASC;
3

Apologies for the lack of data and underlying structure of the tables; thank you though for your answer, it works perfectly. I don't think I would ever have got there on my own. Thank you.