Reputation: 335
I cannot for the life of me figure out where I am going wrong with the insert statements I have created as part of this stored procedure. If I run them outside of the procedure with the variable declared they insert rows without issue. I'm assuming its a simple syntax issue that I am overlooking.
BEGIN
SET NOCOUNT ON;
declare @SixMinCount int = 0
, @SixtyMinCount int = 0
, @Change float = 0.0
, @PercentThreshold float = 0.07
, @CountThreshold int = 900
, @QualityLogNote varchar(500) = ''
select @SixMinCount = COUNT(0)
from dbo.Company c
join dbo.CollectionSite cs with (nolock)
on cs.CompanyId = c.CompanyId
join dbo.SystemCode sc with (nolock)
on sc.SystemCodeId = c.CompanyStateCd
AND sc.SystemCodeTypeId = 12
where c.ActiveInd = 'Y' --only active sites
AND c.ApprovedInd = 'Y' --only approved sites
AND cs.OwningNetworkId = 32971 --fieldprint network
AND cs.TestSiteInd = 'N' --non test sites
AND (select MAX(convert(varchar,lsWorkstationRequest.CreatedDt,120)) from dbo.lsWorkstationRequest where lsWorkstationId = c.CompanyId) > DATEADD(MI, -6, GETDATE()) --has not connected within the last five minutes
AND c.CompanyNm not like 'fp%' --corporate stations and some demo/test stations that aren't marked as such
select @SixtyMinCount = COUNT(0)
from dbo.Company c
join dbo.CollectionSite cs with (nolock)
on cs.CompanyId = c.CompanyId
join dbo.SystemCode sc with (nolock)
on sc.SystemCodeId = c.CompanyStateCd
AND sc.SystemCodeTypeId = 12
where c.ActiveInd = 'Y' --only active sites
AND c.ApprovedInd = 'Y' --only approved sites
AND cs.OwningNetworkId = 32971 --fieldprint network
AND cs.TestSiteInd = 'N' --non test sites
AND (select MAX(convert(varchar,lsWorkstationRequest.CreatedDt,120)) from dbo.lsWorkstationRequest where lsWorkstationId = c.CompanyId) > DATEADD(MI, -60, GETDATE()) --has not connected within the last five minutes
AND c.CompanyNm not like 'fp%' --coporate stations and some demo/test stations that aren't marked as such
IF (@SixMinCount < @CountThreshold )
begin
return 1; -- 1 denotes 'something is wrong'
set @QualityLogNote = ('The total number of Sites connected is below the minimum threshold.'
+ Char(10) + char(13) + 'Sites Connected Number Threshold = '+ convert(varchar, @CountThreshold) + Char(10)+ Char(13) +
'Sites Currently Connected = ' + Convert(varchar, @SixMinCount))
insert into QualityLog (NonconformityCd, OccuranceDt, ActiveInd, DeliveredInd, CorrectedInd, Notes)
values ('FP-IT Site Alert', GetDate(), 'Y', 'N', 'N', @QualityLogNote)
end
--ELSE
begin
select @Change = (@SixtyMinCount-@SixMinCount)/1337.0--.0 to force sql server to use float
if (@Change > @PercentThreshold)
begin
return 1;
set @QualityLogNote = ('There has rapid change in the number of sites connecting in the past 6 minutes'
+ Char(10) + char(13) + 'Threshold for % change in number of sites connected = '+ convert(varchar, Cast(Cast((@PercentThreshold)*100 as decimal(18,2)) as varchar(5)) + '%') + Char(10)+ Char(13) +
'Percent change between cycles = ' + Convert(varchar, Cast(Cast((@Change)*100 as decimal(18,2)) as varchar(5)) + '%'))
insert into QualityLog (NonconformityCd, OccuranceDt, ActiveInd, DeliveredInd, CorrectedInd, Notes)
values ('FP-IT Site Alert', GetDate(), 'Y', 'N', 'N', @QualityLogNote)
end
else
begin
return 0; -- 0 denotes 'all is well'
end
end
END
Upvotes: 1
Views: 160
Reputation: 718
Comment out return 1 above the INSERT statements. The return command interrupts the logic flow, so the code after RETURN is not executed.
Upvotes: 2
Reputation: 38023
When you return 1;
, the procedure stops there.
If you want to insert the row then return 1;
and not continue to the next section, then move the return 1;
after the insert
.
Upvotes: 2