The first check should be

select @RecExists = cast( case when exists (select 1 from TestResult where SCN = @SCN) then 1 else 0 end as bit)

Also, I guess you want to declare this variable as bit if you only want to use it for test of existence.


>Hi gang
>
>What I'm trying to do is create a stored procedure that will
>
>1) Check for existance of a record for a particular SCN
>2) If it exists, check to see if this is a restarted test
>
>For whatever reason (which means I have no clue), the following SP runs with no error, but doesn't produce a result either.
>
>

>USE [DLI_Testing]
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>
>ALTER PROCEDURE [dbo].[TestStart] 
>
> @Subbank		nchar(1),
> @SCN			Nchar(7),
> @TCN			nchar(3),
> @TestSection	nvarchar(1) OUTPUT,
> @TestRestart	nvarChar(1) OUTPUT,
> @ErrMsg		nvarChar(100) OUTPUT
> 
>AS
>BEGIN
>  declare @iError int, @iRowCnt int, @RecExists int, @TotQst int
>  --SET NOCOUNT ON;
>  select @RecExists = 1 where @SCN not in (select SCN from TestResult)
>  if @RecExists = 1
>    Begin
>      begin transaction
>      Insert into TestResult (Subbank,SCN,TCN)
>        values('E0' + @Subbank, @SCN,@Tcn)
>      select @iError = @@error, @iRowCnt = @@rowcount
>      if @iError > 0 or @iRowCnt < 1
>        begin
>         Set @ErrMsg = 'Initial Insert Fails ' + @iError
>         raiserror('Error on Initial Insert into Result -  %d',16,1, @iError) 
>         rollback transaction
>         Return
>        end
>      else
>        commit transaction
>        Select @TestSection = 'L', @TestRestart = 'F'
>    end
>  else	--Record already exists
>    begin
>      select @TestSection = 
>        Case (ListenTotal + ReadTotal)
>          when 25 then 'R'
>          when 0 then 'L'
>          when 50 then 'F'
>        end,
>        @TestRestart = 
>          Case (ListenTotal + ReadTotal)
>            when 25 then 'T'
>            when 0 then 'F'
>            when 50 then 'F'
>          end
>      from TestResult where SCN = @SCN
>    end   
>END
>

>
>Have at it
>Dorris