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

Откуда:
Сообщений: 44
Была процедура, возвращающая рекордсет с уникальными записями. Возникла необходимость запихнуть ее в динамический SQL запрос, с небольшими модификациями. Теперь при запуске новой процедуры возвращаются дублирующиеся записи. Подскажите, пожалуйста, почему?

P.S. DISTINCT стоит...

USE [AMP]
GO
/****** Object: StoredProcedure [dbo].[ClientContact$QueryBySearchCriteriaForGrid] Script Date: 10/10/2009 23:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ClientContact$QueryBySearchCriteriaForGrid]
@ClientId VARCHAR (20)=NULL,
@UserId INT,
@FirstName NVARCHAR (50) = NULL,
@LastName NVARCHAR (50) = NULL,
@Type INT,
@CompanyName NVARCHAR (50) = NULL,
@Email NVARCHAR (50) = NULL,
@Address NVARCHAR (50) = NULL,
@City NVARCHAR (50) = NULL,
@State NVARCHAR (2) = NULL,
@PropertyIds XML = NULL,
@GridPageStartIndex INT,
@GridPageSize INT,
@GridSortingValueSource VARCHAR (250),
@GridSortingDirection VARCHAR (4)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @SortingColumn varchar(250);
SET @SortingColumn = CASE @GridSortingValueSource
WHEN 'Client.Name' THEN 'Client.Name'
WHEN 'Name' THEN 'ISNULL(Contact.LastName, '') + ISNULL(Contact.FirstName, '')'
WHEN 'Client.Type.TextValue' THEN 'Client.Type_ID'
WHEN 'Client.Address' THEN 'Client.Address'
WHEN 'Client.City' THEN 'Client.City'
WHEN 'Client.State.Code' THEN 'Client.[State]'
WHEN 'Client.ZipWithCarrierCode' THEN 'ISNULL(CAST(Client.[Zip] AS VARCHAR(9)), '') + ISNULL(CAST(Client.[Carrier_Code] AS VARCHAR(9)), '')'
WHEN 'Email' THEN 'Contact.[Email]'
ELSE 'Client.Name' END

DECLARE @Sql NVARCHAR(max)
SET @Sql =
N'
SELECT *
FROM
(SELECT DISTINCT
COUNT(*) OVER() as [TotalRowCount],
/*Contact Data*/
Contact.[Client_ID] as [Contact_Client_ID],
Contact.[Client_Contact_ID] as [Client_Contact_ID],
Contact.[FirstName] as [ContactFirstName],
Contact.[LastName] as [ContactLastName],
Contact.[Phone] as [ContactPhone],
Contact.[Fax] as [ContactFax],
Contact.[Email] as [ContactEmail],
Contact.[SD_Created] as [ContactDateCreated],
Contact.[Last_Modified] as [ContactDateModified],
Contact.[DefaultContact] as [DefaultContact],

/*Client Data*/
Client.[User_ID] as [User_ID],
Client.[Client_ID] as [Client_ID],
Client.[Name] as [ClientName],
Client.[Short_Name] as [ClientShortName],
Client.[Address] as [ClientAddress],
Client.[Zip] as [ClientZip],
Client.[Carrier_Code] as [ClientCarrierCode],
Client.[City] as [ClientCity],
Client.[State] as [ClientState],
Client.[Status_ID] as [ClientStatus_ID],
Client.[Type_ID] as [ClientType_ID],
Client.[Comments] AS [ClientComments],
Client.[SD_Created] as [ClientDateCreated],
Client.[Last_Modified] as [ClientDateModified],
Client.[PropertyType] as [ClientPropertyType],
ROW_NUMBER() OVER(ORDER BY ' + @SortingColumn + N' ' + @GridSortingDirection + N') as [RowNum]

FROM
[dbo].[tbl_Client] Client WITH (NOLOCK)
LEFT JOIN [dbo].[tbl_ClientContact] Contact WITH (NOLOCK) ON Contact.Client_ID = Client.Client_ID
LEFT JOIN [dbo].[tbl_ProspectPropertyMap] ppm WITH (NOLOCK) ON ppm.Client_ID = Client.Client_ID

WHERE
(@ClientID IS NULL OR @ClientID LIKE '''' OR Client.[Client_ID] LIKE @ClientID) AND
( @UserId IS NULL OR Client.[User_ID] = @UserId ) AND
( @Type IS NULL OR Client.[Type_ID] = @Type ) AND
( @CompanyName IS NULL OR Client.[Name] LIKE @CompanyName )AND
( @Address IS NULL OR Client.[Address] LIKE @Address ) AND
( @City IS NULL OR Client.[City] LIKE @City ) AND
( @State IS NULL OR Client.[State] = @State ) AND
LIKE @ZipCarrierCode ) AND
( @FirstName IS NULL OR Contact.[FirstName] LIKE @FirstName OR Contact.[LastName] LIKE @FirstName OR @FirstName = ''%%'') AND
( @LastName IS NULL OR Contact.[LastName] LIKE @LastName OR Contact.[FirstName] LIKE @LastName OR @LastName = ''%%'') AND
( @Email IS NULL OR Contact.[Email] LIKE @Email )AND
( @PropertyIds IS NULL OR ppm.Property_ID IN (SELECT C.value(''.'', ''int'') FROM @PropertyIds.nodes(''/properties/id'') T(C))) AND
(Contact.DefaultContact = 1) AND
(Contact.[IsDeleted] = 0)

) as data
WHERE [RowNum] BETWEEN @GridPageStartIndex AND (@GridPageStartIndex + @GridPageSize) - 1'

EXEC sp_executesql @Sql,
N'@ClientId VARCHAR (20)=NULL,
@UserId INT,
@FirstName NVARCHAR (50) = NULL,
@LastName NVARCHAR (50) = NULL,
@Type INT,
@CompanyName NVARCHAR (50) = NULL,
@Email NVARCHAR (50) = NULL,
@Address NVARCHAR (50) = NULL,
@City NVARCHAR (50) = NULL,
@State NVARCHAR (2) = NULL,
@PropertyIds XML = NULL,
@GridPageStartIndex INT,
@GridPageSize INT,
@GridSortingValueSource VARCHAR (250),
@GridSortingDirection VARCHAR (4)',
@ClientId,
@UserId,
@FirstName,
@LastName,
@Type,
@CompanyName,
@Email,
@Address,
@City,
@State,
@PropertyIds,
@GridPageStartIndex,
@GridPageSize,
@GridSortingValueSource,
@GridSortingDirection

END
11 окт 09, 01:51    [7769991]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
Takhir
Member

Откуда:
Сообщений: 44
Дополнение - обнаружил, что дублируются только те записи, которые попадают под условие:

LEFT JOIN [dbo].[tbl_ProspectPropertyMap] ppm WITH (NOLOCK) ON ppm.Client_ID = Client.Client_ID

то есть в этой таблице есть соотв. записи по Client_ID. Сколько там дублирующихся по Client_ID записей, столько и добавляется в результат динамического SQL.

Помогите, объясните, пожалуйста, почему.
11 окт 09, 02:50    [7770011]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
Glory
Member

Откуда:
Сообщений: 104760
Takhir

Помогите, объясните, пожалуйста, почему.

Потому, что вы написали запрос, который возвращает такой результат
11 окт 09, 13:20    [7770149]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Takhir
Помогите, объясните, пожалуйста, почему.
Весь запрос не осилил.

Но такое замечание: дубли в результатах запроса надо не DISTINCT'ом искоренять, а пониманием, что за данные содержатся в таблицах и в каких они отношениях. Учитывая, что у Вас всего три таблицы, это возможно.

ЗЫ. И зачем здесь динамический SQL? Или sql injection хотите себе получить на одно место?
11 окт 09, 13:30    [7770155]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
iljy
Member

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

а distinct тут вам и не поможет, потому что строки у вас содержат ROW_NUMBER, а он естественно будет различен для всех строк. Как-то так. Т.е. либо дистинкт во внутреннем запросе, а ROW_NUMBER во внешнем, либо (что предпочтительнее) прислушайтесь к советам Glory и Senya_L
11 окт 09, 19:40    [7770542]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
Takhir
Member

Откуда:
Сообщений: 44
iljy
Takhir,

а distinct тут вам и не поможет, потому что строки у вас содержат ROW_NUMBER, а он естественно будет различен для всех строк. Как-то так. Т.е. либо дистинкт во внутреннем запросе, а ROW_NUMBER во внешнем, либо (что предпочтительнее) прислушайтесь к советам Glory и Senya_L


Спасибо за советы!

Если я не ошибаюсь, ROW_NUMBER ПРИМЕНЯЕТСЯ к результату. То есть полученный рекордсет просто сортируется посредством ORDER BY, а ROW_NUMBER вернет исходную позицию записи в неотсортированном рекордсете. Поправьте меня, пожалуйста, если я ошибаюсь.

Далее, если я делаю LEFT JOIN к таблице по ID, и в присоединяемой таблице есть три записи для этого ID, но из этой таблицы я НИЧЕГО не возвращаю, а использую ее только для одного из условий WHERE, разве итоговый запрос вернет мне дополнительные две (дублирующиеся) записи?
11 окт 09, 22:44    [7770836]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Takhir

Далее, если я делаю LEFT JOIN к таблице по ID, и в присоединяемой таблице есть три записи для этого ID, но из этой таблицы я НИЧЕГО не возвращаю, а использую ее только для одного из условий WHERE, разве итоговый запрос вернет мне дополнительные две (дублирующиеся) записи?

Истинно так.

Вы, случайно, не путаете функции join с функциями exists?
11 окт 09, 23:01    [7770854]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
iljy
Member

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

ROW_NUMBER применяется до distinct, так что сроки у вас по любому уникальны. А про join вам locky ответил
11 окт 09, 23:43    [7770897]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
если уж "цеплятся к словам", то left join тут в принципе не нужен и используется неправильно.
Данные из tbl_ClientContact используются только тогда, когда задан один из фильтрующих параметров (@firstname,@lastname etc).
Однако наложение фильтра идёт в клаузе where, что сразу превращает left join в inner join.
Хотя, опять-таки, тут нужен не join, а exists.

ps и если я не запутался в скобках, то условие (Contact.DefaultContact = 1) AND (Contact.[IsDeleted] = 0) вообще сводит на нет left join.

-------------------------
There’s no silver bullet!
12 окт 09, 00:05    [7770910]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
Takhir
Member

Откуда:
Сообщений: 44
Спасибо, ребята! С вашей подачи разобрался, в чем косяк, убрал LEFT JOIN на проблемную таблицу, которая дублирующие записи возвращала, и модифицировал следующий кусок в WHERE:

БЫЛО:

( @PropertyIds IS NULL OR ppm.Property_ID IN (SELECT C.value(''.'', ''int'') FROM @PropertyIds.nodes(''/properties/id'') T(C))) AND

СТАЛО:

( @PropertyIds IS NULL OR ((SELECT ppm.Property_ID FROM tbl_ProspectPropertyMap ppm WHERE ppm.Client_ID=Client.Client_ID) IN (SELECT C.value(''.'', ''int'') FROM @PropertyIds.nodes(''/properties/id'') T(C)))) AND

Ожидаемое количество уникальных записей было получено. Мои изменения правильны?
12 окт 09, 00:33    [7770931]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
Takhir
Member

Откуда:
Сообщений: 44
locky
если уж "цеплятся к словам", то left join тут в принципе не нужен и используется неправильно.
Данные из tbl_ClientContact используются только тогда, когда задан один из фильтрующих параметров (@firstname,@lastname etc).
Однако наложение фильтра идёт в клаузе where, что сразу превращает left join в inner join.
Хотя, опять-таки, тут нужен не join, а exists.

ps и если я не запутался в скобках, то условие (Contact.DefaultContact = 1) AND (Contact.[IsDeleted] = 0) вообще сводит на нет left join.

-------------------------
There’s no silver bullet!


По поводу "Однако наложение фильтра идёт в клаузе where, что сразу превращает left join в inner join." полностью согласен. Это не моя проц, но исправляю сейчас я, и это тоже заметил. А чем exists лучше join'a в данном случае?
12 окт 09, 00:37    [7770934]     Ответить | Цитировать Сообщить модератору
 Re: Дублируются записи в динамическом SQL  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Takhir
А чем exists лучше join'a в данном случае?

join и exists, вообще говоря, выполняют разные функции.
12 окт 09, 00:58    [7770949]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить