Bar Load Get Display Stored Procedure...
alter PROCEDURE [dbo].[BarLoad_getDisplay]
@EventID int,
@Attempt varchar(25)
AS
create table #BarLoadDisplay
(
RowNumber int identity (1, 1),
OutLine varchar(500)
)
insert into #BarLoadDisplay
(OutLine)
values
(' Attempting: ' + @Attempt + ' lbs')
insert into #BarLoadDisplay
(OutLine)
values
('')
declare @WeightWork numeric(8,2)
declare @BarWeight2 numeric(8,2)
declare @LockWeight2 numeric(8,2)
declare @Plates numeric(15,8)
declare @Plates2 integer
declare @PlateWord varchar(20)
set @BarWeight2 = isnull(( select top 1 a.BarWeight
from Accounts a join
Users u on u.AccountID = a.AccountID join
Events e on e.CreatedByUserID = u.UserID
where e.EventID = @EventID
),45.0)
set @LockWeight2 = isnull(( select top 1
a.LockWeight
from Accounts a join
Users u on u.AccountID = a.AccountID join
Events e on e.CreatedByUserID = u.UserID
where e.EventID = @EventID
),2.5)
set @WeightWork =
isnull(convert(numeric(8,2),@Attempt) -
@BarWeight2 - @LockWeight2*2,0)
insert into #BarLoadDisplay
(OutLine)
values
(' Load each side of the bar as follows...')
insert into #BarLoadDisplay
(OutLine)
values
('')
DECLARE @weight numeric(8,2),
@Color varchar(25),
@Count int,
@barweight numeric(8,2),
@lockweight numeric(8,2)
DECLARE BarLoadList_cursor CURSOR FOR
select bl.weight, bl.color, bl.count,
a.barweight, a.lockweight
from BarLoad bl join
Users u on u.UserID = bl.CreatedByUserID join
Accounts a on a.AccountID = u.AccountID join
Users u2 on u2.AccountID = a.AccountID join
Events e on e.CreatedByUserID = u2.UserID
where e.EventID = @EventID
and isnull(bl.lbsFlag,'N') = 'Y'
order by bl.weight desc
OPEN BarLoadList_cursor
FETCH NEXT FROM BarLoadList_cursor
INTO @weight, @Color, @Count,
@barweight, @lockweight
WHILE @@FETCH_STATUS = 0 and
@WeightWork > 0
BEGIN
if @WeightWork >= (@Weight * 2.0)
begin
set @Plates =
ROUND((@WeightWork/@Weight),0,1)
if @Plates/2 <> ROUND((@Plates/2),0,1)
begin
set @Plates = @Plates - 1
end
if @Plates > @Count
begin
set @Plates = @Count
end
if @Plates/2 <> ROUND((@Plates/2),0,1)
begin
set @Plates = @Plates - 1
end
set @Plates2 = ROUND((@Plates/2),0,1)
if @Plates2 > 1
begin
set @PlateWord = 'Plates'
end
else
begin
set @PlateWord = 'Plate'
end
insert into #BarLoadDisplay
(OutLine)
values
(' ' + convert(varchar(50),@Plates2) + ' ' +
@Color + ' ' + convert(varchar(50),@weight) +
' lb ' + @PlateWord)
set @WeightWork =
@WeightWork - (@weight * @Plates)
end
FETCH NEXT FROM BarLoadList_cursor
INTO @weight, @Color, @Count, @barweight,
@lockweight
END
CLOSE BarLoadList_cursor
DEALLOCATE BarLoadList_cursor
select OutLine
from #BarLoadDisplay
order by RowNumber