title: Inconsistent results with Service Broker in SQL
body: <p>I am using script from <a href="http://rusanu.com/2009/08/18/passing-parameters-to-a-background-procedure/" rel="nofollow">rusanu.com</a> to execute stored procedures with parameters asynchronously.</p>
  
  <p>I have around 20-30 stored procedures and these stored procedures store the results based on data passed to them in a global temporary table.</p>
  
  <p>But I am getting greatly inconsistent results for exactly the same data. The script works all right otherwise.</p>
  
  <p>Are there any issues as such that may lead to this behavior ?</p>
  
  <p><strong>Edit 1:</strong>  <code>dbo.Match</code> procedure --accessed commonly by those 20-30 procedures.</p>
  
  <pre><code>CREATE Procedure [dbo].[MATCH]
  @parameterFromUser  nvarchar(30),
  @ColumnnName nvarchar(30) ,
  @constantval float(2),
  @checkbool int
  As 
  Begin
  declare @maxim int = 0 
  declare @Matches table
  (
  RowId int not null primary key ,
  PercentMatch decimal(8,3) not null,
  Percentage decimal(18, 4)
  )
  declare @Temp table
  (
  [Row] int,
  VarLength int,
  Counts int,
  MaxMatch int
  )
  declare @tempint int = Len(@parameterFromUser), @counter int = 1, @counterOuter int = 1
  while(@counterOuter &lt;= @tempint)
  begin
  set @counter = @counterOuter 
  while(@counter &lt;= @tempint)
  Begin
  if @checkbool = 0
  begin
  
  Insert into @Temp([Row], Counts, VarLength) 
  select  Rowid, (@counter - @counterOuter) + 1, 
  CASE WHEN @ColumnnName = 'firstname' THEN LEN(FirstName)
       WHEN @ColumnnName = 'middlename' THEN LEN(middlename)
       WHEN @ColumnnName = 'lastname' THEN LEN(LastName)
       WHEN @ColumnnName = 'State' THEN LEN([State])
       WHEN @ColumnnName = 'City' THEN LEN(City)
       WHEN @ColumnnName = 'StreetName' THEN LEN(StreetName)
       WHEN @ColumnnName = 'StreetType' THEN LEN(StreetType)
       WHEN @ColumnnName = 'patientnickname' THEN LEN(patientnickname)
       WHEN @ColumnnName = 'patientsMaidenlastname' THEN LEN(patientsMaidenlastname)
       WHEN @ColumnnName = 'fathersFirstName' THEN LEN(fathersFirstName)
       WHEN @ColumnnName = 'fatherslastname' THEN LEN(fatherslastname)
       WHEN @ColumnnName = 'motherslastname' THEN LEN(motherslastname)
       WHEN @ColumnnName = 'motherslastname' THEN LEN(motherslastname)
       WHEN @ColumnnName = 'mothersMaidenlastname' THEN LEN(mothersMaidenlastname)
       WHEN @ColumnnName = 'citizenship' THEN LEN(citizenship)
       WHEN @ColumnnName = 'nationality' THEN LEN(nationality)
       WHEN @ColumnnName = 'Ethnicity' THEN LEN(Ethnicity)
       WHEN @ColumnnName = 'race' THEN LEN(race)
       WHEN @ColumnnName = 'religion' THEN LEN(religion)
       WHEN @ColumnnName = 'primarylanguage' THEN LEN(primarylanguage)
       WHEN @ColumnnName = 'patientmrn' THEN LEN(patientmrn)
       WHEN @ColumnnName = 'hospitalname' THEN LEN(hospitalname)
       WHEN @ColumnnName = 'Medicaidid' THEN LEN(Medicaidid)
       WHEN @ColumnnName = 'pcpnpi' THEN LEN(pcpnpi)
       WHEN @ColumnnName = 'phonenumber' THEN LEN(phonenumber)
       WHEN @ColumnnName = 'email' THEN LEN(email)
  
  
  End
  from Patients where 
  (case when @ColumnnName = 'firstname' then firstname 
              when @ColumnnName = 'middlename' then middlename 
              when @ColumnnName = 'lastname' then lastname 
              when @ColumnnName = 'State' then [State] 
              when @ColumnnName = 'City' then City 
              when @ColumnnName = 'StreetName' then StreetName 
              when @ColumnnName = 'StreetType' then StreetType 
              when @ColumnnName = 'patientnickname' then patientnickname
              when @ColumnnName = 'patientsMaidenlastname' then patientsMaidenlastname
              when @ColumnnName = 'fathersFirstName' then fathersFirstName
              when @ColumnnName = 'fatherslastname' then fatherslastname
              when @ColumnnName = 'mothersfirstname' then mothersfirstname
              when @ColumnnName = 'motherslastname' then motherslastname
              when @ColumnnName = 'mothersMaidenlastname' then mothersMaidenlastname
              WHEN @ColumnnName = 'citizenship' THEN citizenship
              WHEN @ColumnnName = 'nationality' THEN nationality
              WHEN @ColumnnName = 'Ethnicity' THEN Ethnicity
              WHEN @ColumnnName = 'race' THEN race
              WHEN @ColumnnName = 'religion' THEN religion
             WHEN @ColumnnName = 'primarylanguage' THEN primarylanguage
             WHEN @ColumnnName = 'patientmrn' THEN patientmrn
              WHEN @ColumnnName = 'hospitalname' THEN hospitalname
              WHEN @ColumnnName = 'Medicaidid' THEN Medicaidid
              WHEN @ColumnnName = 'pcpnpi' THEN pcpnpi
              WHEN @ColumnnName = 'phonenumber' THEN phonenumber
              WHEN @ColumnnName = 'email' THEN email
  
  
  
  
  
              end) LIKE + '%' + SUBSTRING(@parameterFromUser, @counterOuter, @counter) + '%' 
  end
  else if @checkbool = 1
  begin
  
  Insert into @Temp([Row], Counts, VarLength) 
  select  p.Rowid, (@counter - @counterOuter) + 1, 
  CASE WHEN @ColumnnName = 'firstname' THEN LEN(FirstName)
       WHEN @ColumnnName = 'middlename' THEN LEN(middlename)
       WHEN @ColumnnName = 'lastname' THEN LEN(LastName)
       WHEN @ColumnnName = 'State' THEN LEN([State])
       WHEN @ColumnnName = 'City' THEN LEN(City)
       WHEN @ColumnnName = 'StreetName' THEN LEN(StreetName)
       WHEN @ColumnnName = 'StreetType' THEN LEN(StreetType)
       WHEN @ColumnnName = 'patientnickname' THEN LEN(patientnickname)
       WHEN @ColumnnName = 'patientsMaidenlastname' THEN LEN(patientsMaidenlastname)
       WHEN @ColumnnName = 'fathersFirstName' THEN LEN(fathersFirstName)
       WHEN @ColumnnName = 'fatherslastname' THEN LEN(fatherslastname)
       WHEN @ColumnnName = 'motherslastname' THEN LEN(motherslastname)
       WHEN @ColumnnName = 'motherslastname' THEN LEN(motherslastname)
       WHEN @ColumnnName = 'mothersMaidenlastname' THEN LEN(mothersMaidenlastname)
       WHEN @ColumnnName = 'citizenship' THEN LEN(citizenship)
       WHEN @ColumnnName = 'nationality' THEN LEN(nationality)
       WHEN @ColumnnName = 'Ethnicity' THEN LEN(Ethnicity)
       WHEN @ColumnnName = 'race' THEN LEN(race)
       WHEN @ColumnnName = 'religion' THEN LEN(religion)
       WHEN @ColumnnName = 'primarylanguage' THEN LEN(primarylanguage)
       WHEN @ColumnnName = 'patientmrn' THEN LEN(patientmrn)
       WHEN @ColumnnName = 'hospitalname' THEN LEN(hospitalname)
       WHEN @ColumnnName = 'Medicaidid' THEN LEN(Medicaidid)
       WHEN @ColumnnName = 'pcpnpi' THEN LEN(pcpnpi)
       WHEN @ColumnnName = 'phonenumber' THEN LEN(phonenumber)
       WHEN @ColumnnName = 'email' THEN LEN(email)
  
  
  End
  from Patients p Cross Apply ( SELECT MAX(ModifiedAt) AS ModifiedAt FROM POI2Version7.dbo.ResultsSTored rs  WHERE  rs.RowId = p.RowId) r
  WHERE   p.ModifiedAt &gt; r.ModifiedAt  and  
  (case when @ColumnnName = 'firstname' then firstname 
              when @ColumnnName = 'middlename' then middlename 
              when @ColumnnName = 'lastname' then lastname 
              when @ColumnnName = 'State' then [State] 
              when @ColumnnName = 'City' then City 
              when @ColumnnName = 'StreetName' then StreetName 
              when @ColumnnName = 'StreetType' then StreetType 
              when @ColumnnName = 'patientnickname' then patientnickname
              when @ColumnnName = 'patientsMaidenlastname' then patientsMaidenlastname
              when @ColumnnName = 'fathersFirstName' then fathersFirstName
              when @ColumnnName = 'fatherslastname' then fatherslastname
              when @ColumnnName = 'mothersfirstname' then mothersfirstname
              when @ColumnnName = 'motherslastname' then motherslastname
              when @ColumnnName = 'mothersMaidenlastname' then mothersMaidenlastname
              WHEN @ColumnnName = 'citizenship' THEN citizenship
              WHEN @ColumnnName = 'nationality' THEN nationality
              WHEN @ColumnnName = 'Ethnicity' THEN Ethnicity
              WHEN @ColumnnName = 'race' THEN race
              WHEN @ColumnnName = 'religion' THEN religion
             WHEN @ColumnnName = 'primarylanguage' THEN primarylanguage
             WHEN @ColumnnName = 'patientmrn' THEN patientmrn
              WHEN @ColumnnName = 'hospitalname' THEN hospitalname
              WHEN @ColumnnName = 'Medicaidid' THEN Medicaidid
              WHEN @ColumnnName = 'pcpnpi' THEN pcpnpi
              WHEN @ColumnnName = 'phonenumber' THEN phonenumber
              WHEN @ColumnnName = 'email' THEN email
  
  
  
  
  
              end) LIKE + '%' + SUBSTRING(@parameterFromUser, @counterOuter, @counter) + '%' 
  end
  set @counter = @counter + 1
  end
  set @counterOuter = @counterOuter + 1 
  end
  
  Insert into @Matches(RowId, PercentMatch, Percentage) 
  select rownumber, MatchingPercent, MatchingPercent * @constantVal
  from (
    select t.[Row] as rownumber,
  cast(Max(Counts) as float)/(Abs(Max(Counts) - case when Max(VarLength) &gt;= @tempint then Max(VarLength) else @tempint end) + Max(Counts))* 100 
  As MatchingPercent
  from @Temp t 
  Group by Row ) s
  select RowId,Percentage from @Matches
  End
  Go
  </code></pre>
  
  <p><strong>Edit 2 :</strong> OutputProcedure: This is the procedure that calls a procedure called <code>Calling procedure</code> which in turn calls service broker to execute SP's in asynch.</p>
  
  <p>======================================================================        </p>
  
  <pre><code>Create Procedure OutputProcedure
   @FirstName nvarchar(20) = null,
   @MiddleName nvarchar(20) = null, 
   @LastName nvarchar(20) = null, 
  @Date int = null,
  @Month int = null,
  @Year int = null,
  @SSN1 nvarchar(3)= null, 
  @SSN2 nvarchar(2)= null,
  @SSN3 nvarchar(4)= null,
  @ZIP nvarchar(10)= null,
  @StateOfResidence nvarchar(2)= null,
   @City nvarchar(20) = null,
  @StreetName nvarchar(20)= null,
  @StreetType nvarchar(20)= null,
  @BuildingNumber int= null,
  @Aptnumber nvarchar(10)= null,
  @patientNickName nvarchar(20)= null,
  @patientMaidenLN nvarchar(20)= null,
  @FatherFN nvarchar(20)= null,
  @FatherLN nvarchar(20)= null,
  @MotherFN nvarchar(20)= null,
  @MotherLN nvarchar(20)= null,
  @MotherMaidenLN nvarchar(20)= null,
  @Citizenship nvarchar(20)= null,
  @Nationality nvarchar(20)= null,
  @Ethnicity nvarchar(20)= null,
  @Race nvarchar(20)= null,
  @Religion nvarchar(20)= null,
  @primarylanguage nvarchar(20)= null,
  @patientmrn nvarchar(128) = null,
  @hospitalname nvarchar(30)= null,
  @Medicaidid nvarchar(10) = null,
  @pcpnpi nvarchar(10) = null,
  @phonenumber nvarchar(128) = null,
  @email nvarchar(30) = null
  
  AS
  BEGIN
  SET NOCOUNT ON 
  declare  @cnt float(2) = 0, @searchNumber int = 0
  
  select @searchNumber = NEXT VALUE FOR  OutputProcedureCounter
  print (@searchNumber)
  
   IF OBJECT_ID('tempdb.dbo.##Temp2') Is not null
  Begin
  drop table ##Temp2
  End
  
  create table ##Temp2
  (
  Rownumber int not null,
  ValFromUser nvarchar(20),
  Percentage decimal(18, 4) not null
  )
  
  
  --declare @constVal decimal(18,4) = 0
  declare @checkbool int
  declare @StringConcat nvarchar(600) =  ISNULL(@firstname, '') + '~' + ISNULL(@middlename, '') + '~' + ISNULL(@lastname, '') + '~' + ISNULL(@City, '') + '~' + Cast(ISNULL(@Date, '') As nvarchar(2)) + '~' + Cast(ISNULL(@Month, '') As nvarchar(2)) + '~' + Cast(ISNULL(@Year, '') As nvarchar(4)) + '~' + Cast(ISNULL(@SSN1, '') as nvarchar(3)) + '~' + Cast(ISNULL(@SSN2, '') As nvarchar(2)) + '~' + Cast(ISNULL(@SSN3, '') As nvarchar(4)) + '~' + Cast(ISNULL(@ZIP, '') as nvarchar(10)) + '~' + ISNULL(@StateOfResidence, '') + '~' + ISNULL(@StreetName, '') + '~' + ISNULL(@StreetType, '') + '~' + Cast(ISNULL(@BuildingNumber, '') As nvarchar(10)) + '~' + Cast(ISNULL(@AptNumber, '') As nvarchar(10)) + '~' + ISNULL(@PatientNickName, '') + '~' + ISNULL(@MotherMaidenLN, '') + '~' + ISNULL(@FatherFN, '') + '~' + ISNULL(@FatherLN, '') + '~' + ISNULL(@MotherFN, '') + '~' + ISNULL(@MotherLN, '') + '~' + ISNULL(@MotherMaidenLN, '') + '~' + ISNULL(@citizenship, '') +'~' + ISNULL(@Nationality, '')  + '~' + ISNULL(@Ethnicity, '') + '~' + ISNULL(@Race, '')  +'~' + ISNULL(@religion, '')  + '~' +  ISNULL(@primarylanguage, '') + '~' + ISNULL(@patientmrn, '') + '~' + ISNULL(@hospitalname, '') + '~' + ISNULL(@medicaidid, '') + '~' + ISNULL(@pcpnpi, '') + '~' + ISNULL(@phonenumber, '') + '~' + ISNULL(@email, '')
  
  if exists(select 1 from dbo.ResultsStored where Hashedkey = HashBytes('MD5', @StringConcat))
  begin
  set @checkbool = 0
  --if exists(select 1 from ResultsStored rs join POI2Version7.dbo.Patients p on rs.rowId = p.rowId where p.ModifiedAt &gt; rs.ModifiedAt)
  if exists(select * from Patients p Cross Apply ( SELECT MAX(ModifiedAt) AS ModifiedAt FROM ResultsSTored rs  WHERE  rs.RowId = p.RowId) r
  WHERE   p.ModifiedAt &gt; r.ModifiedAt)
  begin
  
  print 'begin exists'
  set @checkbool = 1
  Exec CallingProcedure @FirstName = @FirstName,
   @MiddleName = @MiddleName, 
   @LastName = @LastName , 
  @Date = @Date ,
  @Month = @Month,
  @Year = @Year ,
  @SSN1= @SSN1, 
  @SSN2 = @SSN2 ,
  @SSN3 = @SSN3 ,
  @ZIP = @ZIP ,
  @StateOfResidence = @StateOfResidence,
   @City = @City ,
  @StreetName =@StreetName,
  @StreetType = @StreetType,
  @BuildingNumber= @BuildingNumber,
  @Aptnumber = @Aptnumber,
  @patientNickName = @patientNickName ,
  @patientMaidenLN = @patientMaidenLN,
  @FatherFN = @FatherFN,
  @FatherLN = @FatherLN,
  @MotherFN  = @MotherFN ,
  @MotherLN = @MotherLN,
  @MotherMaidenLN = @MotherMaidenLN,
  @Citizenship  = @Citizenship ,
  @Nationality = @Nationality,
  @Ethnicity = @Ethnicity ,
  @Race  = @Race ,
  @Religion = @Religion ,
  @primarylanguage = @primarylanguage,
  @patientmrn = @patientmrn ,
  @hospitalname = @hospitalname ,
  @Medicaidid = @Medicaidid,
  @pcpnpi= @pcpnpi ,
  @phonenumber = @phonenumber ,
  @email = @email,
  @checkbool = @checkbool,
  @cnt = @cnt output 
  
  print 'updating results table'
  Update dbo.ResultsStored 
  set dbo.ResultsStored.FinalSearchSeral = @searchNumber, 
      dbo.ResultsStored.ModifiedAt = getDate(), 
      dbo.ResultsStored.PercentMatch = t.Perc 
  from 
      dbo.ResultsStored
  join
      (select Rownumber,(SUM(Percentage) / @cnt) as Perc 
       from ##Temp2 
       GROUP BY Rownumber) t
  on dbo.ResultsStored.RowId=t.Rownumber
     and dbo.ResultsStored.HashedKey = HASHBYTES('MD5', @StringConcat)
     end
     IF OBJECT_ID('tempdb..dbo.##Temp2') Is not null
  Begin
  PRINT 'DROPPED'
  DROP TABLE ##Temp2
  End
  select * from dbo.ResultsStored where dbo.ResultsStored.HashedKey = HASHBYTES('MD5', @StringConcat) order by FinalSearchSeral desc, PercentMatch desc
  
  
  
  end
  
   else if not exists(select 1 from dbo.ResultsStored where Hashedkey = HashBytes('MD5', @StringConcat))
  begin
  print 'insert record';
  set @checkbool = 0;
  Exec CallingProcedure @FirstName = @FirstName,
   @MiddleName = @MiddleName, 
   @LastName = @LastName , 
  @Date = @Date ,
  @Month = @Month,
  @Year = @Year ,
  @SSN1= @SSN1, 
  @SSN2 = @SSN2 ,
  @SSN3 = @SSN3 ,
  @ZIP = @ZIP ,
  @StateOfResidence = @StateOfResidence,
   @City = @City ,
  @StreetName =@StreetName,
  @StreetType = @StreetType,
  @BuildingNumber= @BuildingNumber,
  @Aptnumber = @Aptnumber,
  @patientNickName = @patientNickName ,
  @patientMaidenLN = @patientMaidenLN,
  @FatherFN = @FatherFN,
  @FatherLN = @FatherLN,
  @MotherFN  = @MotherFN ,
  @MotherLN = @MotherLN,
  @MotherMaidenLN = @MotherMaidenLN,
  @Citizenship  = @Citizenship ,
  @Nationality = @Nationality,
  @Ethnicity = @Ethnicity ,
  @Race  = @Race ,
  @Religion = @Religion ,
  @primarylanguage = @primarylanguage,
  @patientmrn = @patientmrn ,
  @hospitalname = @hospitalname ,
  @Medicaidid = @Medicaidid,
  @pcpnpi= @pcpnpi ,
  @phonenumber = @phonenumber ,
  @email = @email,
  @checkbool = @checkbool,
  @cnt = @cnt output 
  select SUM(Percentage) as SUM , @cnt as Count , (SUM(Percentage)/@cnt) as Percentage from ##Temp2 group By RowNumber order by Percentage desc
  Insert into dbo.ResultsStored( searchSerial,FinalSearchSeral, StringSearched, RowId,PercentMatch, HashedKey) 
  select @searchNumber, @searchNumber,  dbo.encrypt(@StringConcat), RowNumber, (SUM(Percentage)/@cnt) as Percentage , HASHBYTES('MD5', @StringConcat)
  FROM ##Temp2 GROUP BY RowNumber
  select * from dbo.ResultsStored order by ModifiedAt desc, PercentMatch desc
  end
  
  end
  </code></pre>