/*
select dbo.ve_GetTransitionErrorCount ()
print 'testing ' + cast(dbo.ve_GetTransitionErrorCount() as varchar(20))
*/

CREATE FUNCTION [dbo].[ve_GetTransitionErrorCount] () RETURNS int
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @Count int
select @Count = count(*) from (
select dbo.ve_GetShortPathFromContentId(contentid) location, * from (
select contentid, text, tt.*,
case when text like '%'+ case when tt.toid = tt.rangeid and tt.IsRange = 0
then 
'#Link:Transition:' + cast(TranType as nvarchar(2)) + 
' ' + cast(TransitionID as nvarchar(10)) + ' ' + cast(ToID as nvarchar(10)) + '%' 
else
'#Link:TransitionRange:' + cast(TranType as nvarchar(2)) + 
' ' + cast(TransitionID as nvarchar(10)) + ' ' + cast(ToID as nvarchar(10)) + ' ' + cast(RangeID as nvarchar(10))+ '%' 
end then 'matches' else 'different' end ContentMatchesTrans, 
case when text like '%'+ 
'#Link:TransitionRange:' + cast(TranType as nvarchar(2)) + 
' ' + cast(TransitionID as nvarchar(10)) + ' ' + cast(ToID as nvarchar(10)) + ' ' + cast(RangeID as nvarchar(10))+ '%' 
then 'matches' else 'different' end ContentMatchesRangeTrans
from Contents cc
join transitions tt on tt.fromid = cc.contentid) mm
where ContentMatchesTrans = 'different') mm
return @Count END