Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Здравствуйте, уважаемые форумчане!

Есть следующая проблема

существует хранимая процедура

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ic_terapevt_temp] 
 @date_begin DATETIME,
 @date_end DATETIME,
 @first_name varchar(100) = NULL,
 @last_name varchar(100) = NULL,
 @father_name varchar(100) = NULL,
 @donor_id BIGINT = NULL,
 @visits_id BIGINT = NULL,
 @state INT = NULL
AS 
SELECT 
 dv.date_registration, 
 dd.first_name, 
 dd.last_name, 
 dd.father_name 
FROM 
 dbo.donor_visits dv INNER JOIN
 dbo.donor_visits_steps dvs on dv.id = dvs.id_donor_visits INNER JOIN
 dbo.donor_details dd on dv.id_donnor = dd.donor_id LEFT JOIN
 dbo.rollback_donor rd on dvs.id = rd.id_donor_visits 
WHERE 
 (dv.consultation IS NULL OR dv.consultation = 0 OR (dv.consultation = 1 AND dv.admission = 1)) AND
 (
  ( 
   (dv.id_donor_type = 1) AND 
   (dvs.laboratory = 1) AND 
   (@date_begin IS NULL OR dv.date_1 BETWEEN @date_begin AND @date_end)
  ) or
  (
   (dv.id_donor_type = 2) AND 
   (dv.date_2 IS NOT NULL) AND 
   (dv.extrenniy = 0) AND 
   (@date_BEGIN IS null OR dv.date_2 BETWEEN @date_begin AND @date_end)
  ) or
  (
   (dv.id_donor_type = 2) AND 
   (dvs.laboratory = 1) AND 
   (dv.extrenniy = 1) AND 
   (@date_BEGIN IS null OR dv.date_1 BETWEEN @date_begin AND @date_end)
  ) or 
  (
   (dv.use_previous_assay = 1) AND 
   (@date_BEGIN IS null OR dv.date_1 BETWEEN @date_begin AND @date_end)
  ) 
 ) AND

 (@first_name IS null OR dd.first_name LIKE @first_name+'%') AND 
 (@last_name IS null OR dd.last_name LIKE @last_name+'%') AND
 (@father_name IS null OR dd.father_name LIKE @father_name+'%') AND
 (@donor_id is NULL OR dd.donor_id = @donor_id) AND 
 (@visits_id is NULL OR dv.id = @visits_id) AND
 
 (@state IS NULL OR 
  (
  	((@state = 1) AND (dvs.terapevt = 1) AND (rd.id IS NULL)) OR
  	((@state = 2) AND (dvs.terapevt = 0) AND (rd.id IS NULL)) OR
  	((@state = 3) AND (rd.id IS NOT NULL))
  )
 ) AND
 
 (dd.reg_date = (SELECT MAX(reg_date) FROM donor_details WHERE donor_id = dv.id_donnor)) 

ORDER BY 
  dv.date_registration

GO


при выполнении ее в Management Studio

EXEC [ic_terapevt_temp];1 
@date_begin='20120508 00:00:00',
@date_end='20120508 23:59:59',
@first_name=null,
@last_name=null,
@father_name=null,
@donor_id=null,
@visits_id=null,
@state=1


выдается результат

Модератор: Не забываем о 152-ФЗ...



date_registration first_name last_name father_name
----------------------- ------------ -------------- ----------------


если выполнять тело процедуры как запрос

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @date_begin DATETIME
DECLARE @date_end DATETIME
DECLARE @first_name VARCHAR(1000)
DECLARE @last_name VARCHAR(1000)
DECLARE @father_name VARCHAR(1000)
DECLARE @donor_id BIGINT
DECLARE @visits_id BIGINT
DECLARE @state INT

SET @date_begin='20120508 00:00:00'
SET @date_end='20120508 23:59:59'
SET @first_name=null
SET @last_name=null
SET @father_name=null
SET @donor_id=null
SET @visits_id=null
SET @state=3

SELECT distinct 
 dv.date_registration, 
 dd.first_name, 
 dd.last_name, 
 dd.father_name 
FROM 
 dbo.donor_visits dv INNER JOIN
 dbo.donor_visits_steps dvs on dv.id = dvs.id_donor_visits INNER JOIN
 dbo.donor_details dd on dv.id_donnor = dd.donor_id LEFT JOIN
 dbo.rollback_donor rd on dvs.id = rd.id_donor_visits 
WHERE 
 (dv.consultation IS NULL OR dv.consultation = 0 OR (dv.consultation = 1 AND dv.admission = 1)) AND
 (
  ( 
   (dv.id_donor_type = 1) AND 
   (dvs.laboratory = 1) AND 
   (@date_begin IS NULL OR dv.date_1 BETWEEN @date_BEGIN AND @date_end)
  ) or
  (
   (dv.id_donor_type = 2) AND 
   (dv.date_2 IS NOT NULL) AND 
   (dv.extrenniy = 0) AND 
   (@date_BEGIN IS null OR dv.date_2 BETWEEN @date_BEGIN AND @date_end)
  ) or
  (
   (dv.id_donor_type = 2) AND 
   (dvs.laboratory = 1) AND 
   (dv.extrenniy = 1) AND 
   (@date_BEGIN IS null OR dv.date_1 BETWEEN @date_BEGIN AND @date_end)
  ) or 
  (
   (dv.use_previous_assay = 1) AND 
   (@date_BEGIN IS null OR dv.date_1 BETWEEN @date_BEGIN AND @date_end)
  ) 
 ) AND

 (@first_name IS null OR dd.first_name LIKE @first_name+'%') AND 
 (@last_name IS null OR dd.last_name LIKE @last_name+'%') AND
 (@father_name IS null OR dd.father_name LIKE @father_name+'%') AND
 (@donor_id is NULL OR dd.donor_id = @donor_id) AND 
 (@visits_id is NULL OR dv.id = @visits_id) AND
 
 (@state IS NULL OR 
  (
  	((@state = 1) AND (dvs.terapevt = 1) AND (rd.id IS NULL)) OR
  	((@state = 2) AND (dvs.terapevt = 0) AND (rd.id IS NULL)) OR
  	((@state = 3) AND (rd.id IS NOT NULL))
  )
 ) AND
 
 (dd.reg_date = (SELECT MAX(reg_date) FROM donor_details WHERE donor_id = dv.id_donnor)) 

ORDER BY 
  dv.date_registration
GO


то выдается результат

date_registration       first_name   last_name      father_name


база данных стоит на SQL Server 2008 v10.0.16600

Не работает следующее условие

(@state IS NULL OR
(
((@state = 1) AND (dvs.terapevt = 1) AND (rd.id IS NULL)) OR
((@state = 2) AND (dvs.terapevt = 0) AND (rd.id IS NULL)) OR
((@state = 3) AND (rd.id IS NOT NULL))
)
) AND

помогите разобраться в чем проблема

Сообщение было отредактировано: 8 май 12, 18:33
8 май 12, 13:53    [12523402]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
решение найдено
8 май 12, 13:59    [12523426]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
Glory
Member

Откуда:
Сообщений: 104751
процедура вызывается со @state=1, а запрос - со SET @state=3
8 май 12, 14:03    [12523451]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Glory,

Да я заметил это (копи паст неверный), немного разбираясь дальше заметил следующую проблему

ALTER PROCEDURE [dbo].[ic_terapevt_temp] 
 @date_begin DATETIME,
 @date_end DATETIME,
 @state INT = NULL
AS 
SELECT 
 dv.id,

 CASE 
  WHEN rd.id IS NOT NULL THEN rd.result
  WHEN t.donation_type_id IS NOT NULL THEN 'Статус 1'
  WHEN t.donation_type_id IS NULL THEN 'Статус 2'
  WHEN t.change_donation_type = 1 THEN 'Статус 3'
  ELSE ''
 END AS dt_name
 
FROM 
 dbo.donor_visits dv INNER JOIN
 dbo.donor_visits_steps dvs on dv.id = dvs.id_donor_visits LEFT JOIN
 dbo.terapevt t on t.id_visit_steps = dv.id LEFT JOIN
 dbo.rollback_donor rd on rd.id_donor_visits = dv.id
WHERE 
 (dv.date_1 BETWEEN @date_begin AND @date_end) AND

 (
  (@state = 1 AND dvs.terapevt = 1 AND rd.id IS NULL) OR
  (@state = 2 AND dvs.terapevt = 0 AND rd.id IS NULL) OR
  (@state = 3 AND rd.id IS NOT NULL)
 ) 
 
GO

EXEC ic_terapevt_temp;1 
@date_begin='20120508 00:00:00',
@date_end='20120508 23:59:59',
@state=3
GO


возвращает результат

id                   dt_name
-------------------- -------------------------
1610019681           Статус 1
1610019682           Статус 1
1610019683           Статус 1
1610019684           Статус 1
1610019685           Статус 1
1610019686           Статус 1
1610019687           Статус 1
1610019688           Статус 1
1610019689           Статус 1
1610019690           ГЕМОГЛОБИН ВЫШЕ НОРМЫ
1610019691           Статус 1
1610019692           Статус 1
1610019693           Статус 1
1610019694           Статус 1
1610019695           Статус 1
1610019696           Статус 1
1610019697           Статус 1
1610019698           Статус 1
1610019699           Статус 1
1610019700           Статус 1
1610019701           Статус 1
1610019702           Статус 1
1610019703           Статус 1
1610019704           Статус 1
1610019705           Статус 1
1610019706           Гемоглобин ниже нормы


а если выполнять запрос

DECLARE
 @date_begin DATETIME = '20120508 00:00:00',
 @date_end DATETIME = '20120508 23:59:59',
 @state INT = 3

SELECT 
 dv.id,

 CASE 
  WHEN rd.id IS NOT NULL THEN rd.result
  WHEN t.donation_type_id IS NOT NULL THEN 'Статус 1'
  WHEN t.donation_type_id IS NULL THEN 'Статус 2'
  WHEN t.change_donation_type = 1 THEN 'Статус 3'
  ELSE ''
 END AS dt_name
 
FROM 
 dbo.donor_visits dv INNER JOIN
 dbo.donor_visits_steps dvs on dv.id = dvs.id_donor_visits LEFT JOIN
 dbo.terapevt t on t.id_visit_steps = dv.id LEFT JOIN
 dbo.rollback_donor rd on rd.id_donor_visits = dv.id
WHERE 
 (dv.date_1 BETWEEN @date_begin AND @date_end) AND

 (
  (@state = 1 AND dvs.terapevt = 1 AND rd.id IS NULL) OR
  (@state = 2 AND dvs.terapevt = 0 AND rd.id IS NULL) OR
  (@state = 3 AND rd.id IS NOT NULL)
 ) 
GO


то возвращает

id                   dt_name
-------------------- -------------------------
1610019690           ГЕМОГЛОБИН ВЫШЕ НОРМЫ
1610019706           Гемоглобин ниже нормы


если убрать поле dt_name в хранимой процедуре

 CASE 
  WHEN rd.id IS NOT NULL THEN rd.result
  WHEN t.donation_type_id IS NOT NULL THEN 'Статус 1'
  WHEN t.donation_type_id IS NULL THEN 'Статус 2'
  WHEN t.change_donation_type = 1 THEN 'Статус 3'
  ELSE ''
 END AS dt_name


то результат будет такой же как и у запроса по количеству строк
8 май 12, 15:21    [12523768]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
По поводу 152-ФЗ, данные тестовые генерируемые программой
8 май 12, 21:24    [12524901]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Если разбить данный в процедуре кейс на части
ALTER PROCEDURE [dbo].[ic_terapevt_temp] 
 @date_begin DATETIME,
 @date_end DATETIME,
 @state INT = NULL
AS 
SELECT 
 dv.id,
  
/*
 CASE 
  WHEN rd.id IS NOT NULL THEN rd.result
  WHEN t.donation_type_id IS NOT NULL THEN 'Статус 1'
  WHEN t.donation_type_id IS NULL THEN 'Статус 2'
  WHEN t.change_donation_type = 1 THEN 'Статус 3'
  ELSE ''
 END AS dt_name
*/

 CASE 
  WHEN rd.id IS NOT NULL THEN rd.result
  ELSE ''
 END AS rd_res,

 CASE 
  WHEN t.donation_type_id IS NOT NULL THEN 'Статус 1'
  WHEN t.donation_type_id IS NULL THEN 'Статус 2'
  WHEN t.change_donation_type = 1 THEN 'Статус 3'
  ELSE ''
 END AS dt_name
 
FROM 
 dbo.donor_visits dv INNER JOIN
 dbo.donor_visits_steps dvs on dv.id = dvs.id_donor_visits LEFT OUTER JOIN
 dbo.terapevt t on t.id_visit_steps = dv.id LEFT OUTER JOIN
 dbo.rollback_donor rd on rd.id_donor_visits = dv.id
WHERE 
 (dv.date_1 BETWEEN @date_begin AND @date_end) AND

 (
  (@state = 1 AND dvs.terapevt = 1 AND rd.id IS NULL) OR
  (@state = 2 AND dvs.terapevt = 0 AND rd.id IS NULL) OR
  (@state = 3 AND rd.id IS NOT NULL)
 ) 
 
GO

EXEC ic_terapevt_temp;1 
@date_begin='20120508 00:00:00',
@date_end='20120508 23:59:59',
@state=3
GO


то результат будет верным как и в запросе

id           rd_res                   dt_name
------------ ------------------------ --------
1610019690   ГЕМОГЛОБИН ВЫШЕ НОРМЫ    Статус 1
1610019706   Гемоглобин ниже нормы    Статус 1
8 май 12, 22:12    [12525054]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Я заметил, что план выполнения запроса и процедуры разные, начал копать индексы.

нашел следующий индекс

CREATE NONCLUSTERED INDEX [IX_rollback_donor_id_donor_visits] ON [dbo].[rollback_donor] 
(
 [id_donor_visits] ASC
) 
WITH (
 PAD_INDEX  = OFF, 
 STATISTICS_NORECOMPUTE  = OFF, 
 SORT_IN_TEMPDB = OFF, 
 IGNORE_DUP_KEY = OFF, 
 DROP_EXISTING = OFF,  
 ONLINE = OFF,  
 ALLOW_ROW_LOCKS  = ON, 
 ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
GO


после удаления данного индекса процедура вернула верное количество записей

задача решена, но вот только я не понял как влиял индекс на отображение данных
8 май 12, 22:47    [12525187]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Покажите планы выполнения обоих запросов.
8 май 12, 23:03    [12525271]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
План запроса

План хранимой процедуры
9 май 12, 05:43    [12525819]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Вот еще на всякий случай

К сообщению приложен файл (План.zip - 8Kb) cкачать
9 май 12, 05:44    [12525820]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
mnc
Я заметил, что план выполнения запроса и процедуры разные, начал копать индексы.
задача решена, но вот только я не понял как влиял индекс на отображение данных
Выглядит как баг, который скорее всего уже исправлен.
10.0.1600.22(RTM) - это не солидно. Откройте для себя мир сервис паков, обновитесь до SP3 и попробуйте еще раз, с индексом и без.
9 май 12, 20:25    [12527303]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Mind,

Работа на сервере ведется удаленно и у меня нет прав на установку или обновления сервера, сообщил ихнему администратору по поводу обновления, посмотрю что получится.

Спасибо за совет.
9 май 12, 21:31    [12527466]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
mnc
Member

Откуда:
Сообщений: 10
Проблема решилась сразу после установки 1ого сервис пака.
Спасибо всем за помощь.
11 май 12, 07:25    [12533843]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение хранимой процедуры и запроса с тем же кодом отображают разные результаты  [new]
devuser
Member

Откуда: Prague
Сообщений: 108
mnc
Проблема решилась сразу после установки 1ого сервис пака.
Спасибо всем за помощь.




было похожее и некластерный тоже был

По всем признакам решили, что это:

бага

пользователи тоже не хотели обновляться, пока им в конкретную ошибку не ткнули
11 май 12, 08:23    [12533918]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить