Good day.

Today I ran into this:

If you execute the stored procedure (exec procedure param1, param2) and take its text and just execute it , the result will be different . I honestly do not understand what's the matter.

Please point out my mistakes (it is unlikely to be a SM SQL bug, I think so)

That's what I'm doing:

  1. Running the storage:

    exec [dbo].[proc_GetClientsSum] @dateFrom, @dateTo

  2. The text of the same is stored only in the form of a regular script:

     DECLARE @dateFrom datetime = '2016.09.01 00:00:00' DECLARE @dateTo datetime = '2016.09.30 23:59:59' DECLARE @DELIVERED int = 9; DECLARE @SENT int = 2; DECLARE @GSM_PROVIDER int = 5; DECLARE @gmsMessages table ( messageId int, recipient varchar(20), [count] int ) ;WITH CTE AS ( SELECT m.messageId, m.recipient, ms.providerId, ms.statusValue.value('(//Count/text())[1]', 'int') [count], ms.statusId, ROW_NUMBER() OVER (PARTITION BY ms.messageId ORDER BY ms.messageStatusId DESC) rowNumber FROM [dbo].[Messages] m (NOLOCK) INNER JOIN [dbo].[MessageStatus] (NOLOCK) ms ON m.messageId = ms.messageId WHERE m.[dt] >= @dateFrom AND m.[dt] < @dateTo AND statusId = @SENT ) INSERT INTO @gmsMessages(messageId, recipient, [count]) SELECT messageId, recipient, [count] FROM CTE c WHERE providerId = @GSM_PROVIDER AND rowNumber = 1 DECLARE @gmsMessagesFull table ( messageId int, recipient varchar(20), lastStatusId int, [count] int ) INSERT INTO @gmsMessagesFull(messageId, recipient, lastStatusId, [count]) SELECT g.messageId, g.recipient, ( SELECT TOP 1 statusId FROM [dbo].[MessageStatus] ms WHERE ms.messageId = g.messageId ORDER BY messageStatusId DESC ) AS lastStatusId, g.[count] FROM @gmsMessages g SELECT COUNT(*), SUM([dbo].func_GetGmsSmsCost (g.[recipient], g.[count])) FROM @gmsMessagesFull g WHERE g.lastStatusId IN (2, 6, 7, 9, 11)

Personally, I think that the result in both cases should be the same. because in fact we are launching the same mechanism.

UPD: Results

exec SP
852458 | 193089.6790
t-sql:
880444 | 199485.5420

  • If you have duplicates with uncertainty (especially order by ), then the result is often different - you come across these. One time chooses one for the main thing, the second time another. All the same, show the results 1 and 2 if not difficult ... - nick_n_a
  • Sorry, "duplicates" with uncertainty, what is it like? - Leonard Bertone
  • 2
    In the input dates - exactly datetime and not date ? - teran
  • one
    "doubles" with uncertainty, what is it like? This is when you write the ORDER BY field, and you have 2 entries with the same field value. In one sample you can get an earlier record, and then a later one, and in the other way around. The results are added to the question. And you did not try to compare the very table variables that you get in the process of execution? - Akina
  • 2
    Yes, the problem is that the DATE type of parameters is DATE (!!!!) - Leonard Bertone

0