VPF::Помогите построить правильный SELECT

Juju

Дата 13.5.2008, 17:56 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

В журнале MAXIM, есть рубрика: "Задай глупый вопрос и получи умный ответ", я долго пытался найти выход сам, т.к. знаю, что проблема не сложная, но сдался и решил попросить помощи у Вас!

Я не знаток MS SQL вообще, а только вынужден работать с неработающим чужим приложением...
К делу:
Есть две громадные таблицы с большим кол-вом полей... я уменьшу их в примере для читаемости и понимания.
Есть форма запроса, где в edit-ах (которые относятся к полям таблицы) пользователь может набирать параметры запроса
(Учавствуют для соединения с сервером TADOConnection, TADODataSet и в принципе все работает)

На Delphi написана следующая процедура (возьмем к примеру 5 полей, названия тоже упрощены для наглядности):

Код


ADODataSet.Close;
ADODataSet.CommandText := 'SELECT_MY_TABLE;1';
ADODataSet.Parameters.Refresh;
//FirstName
ADODataSet.Parameters[1].Value := Edit1.Text;
//LastName
ADODataSet.Parameters[2].Value := Edit2.Text;
//Age
ADODataSet.Parameters[3].Value := IntEdit.AsInteger;
//Phone
ADODataSet.Parameters[4].Value := Edit4.Text;
//DateBirth
ADODataSet.Parameters[5].Value := DateEdit.Date;
ADODataSet.Open;

На MS SQL Server 2000 написана процедура:

Код


CREATE PROCEDURE SELECT_MY_TABLE(
@FirstName nvarchar(15),
@LastName nvarchar(20),
@Age int,
@Phone nvarchar(12)
@DateBirth datetime
) 
AS
  SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5
  FROM MYTABLE 
  WHERE 
  FIELD1 = @FirstName AND
  FIELD2 = @LastName AND
  FIELD3 = @Age AND
  FIELD4 = @Phone AND
  FIELD5 = @DateBirth
GO

Если все поля поиска заданы - поиск работает, а если передается параметр NULL (что конечно же будет) результаты поиска приводят к неверным результатам (попросту не находят заданные значения)

Подскажите как правильно написать SELECT, чтобы в запросе присутствовали только те поля (параметры) которые заданы пользователем, а упущенные (параметр NULL) игнорировались (упускались) в запросе...
На MS ACCESS я просто формировал строку запроса в Delphi в зависимости от заполнения пользователем поисковых полей и отсылал ее в Query... А здесь хотелось бы обойтись минимальной логикой в программе...

Это сообщение отредактировал(а) Juju - 13.5.2008, 18:14

Magnifico

Дата 13.5.2008, 20:10 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 418
Регистрация: 23.1.2008
Где: Московская област ь

Репутация: 10
Всего: 17

Цитата

здесь хотелось бы обойтись минимальной логикой в программе...

проверять на null каждый параметр и формировать разные запросы по условию

Код


if isnull(@DateBirth)  --убираем поле FIELD5 из запроса
begin 
SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5
  FROM MYTABLE 
  WHERE 
  FIELD1 = @FirstName AND
  FIELD2 = @LastName AND
  FIELD3 = @Age AND
  FIELD4 = @Phone 
end

Это сообщение отредактировал(а) Magnifico - 13.5.2008, 20:11

--------------------

Всё в порядке - спасибо зарядке !

Juju

Дата 13.5.2008, 20:26 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Magnifico, спасибо за ответ... т.е. предлагается:

Если параметр @DateBirth = null - строим запрос без поля FIELD5

Код


if isnull(@DateBirth)  --убираем поле FIELD5 из запроса
begin 
SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5
  FROM MYTABLE 
  WHERE 
  FIELD1 = @FirstName AND
  FIELD2 = @LastName AND
  FIELD3 = @Age AND
  FIELD4 = @Phone 
end

Если параметр @Phone = null - строим запрос без поля FIELD4

Код


if isnull(@Phone)  --убираем поле FIELD4 из запроса
begin 
SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5
  FROM MYTABLE 
  WHERE 
  FIELD1 = @FirstName AND
  FIELD2 = @LastName AND
  FIELD3 = @Age AND
  FIELD5 = @DateBirth
end

Если параметр @Phone=null и @Age=null - строим запрос без поля FIELD4, FIELD3

Код


if isnull(@Phone)  --убираем поле FIELD3, FIELD4 из запроса
if isnull(@Age)  
begin 
SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5
  FROM MYTABLE 
  WHERE 
  FIELD1 = @FirstName AND
  FIELD2 = @LastName AND
  FIELD5 = @DateBirth
end

И так перебираем все комбинации? Даже если поисковых параметров до 20-ти?????? smile

Itsys

Дата 13.5.2008, 20:42 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

Что мешает построить запрос в текстовом виде и выполнить его через exec()?

Juju

Дата 13.5.2008, 21:01 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Itsys это типа таким образом...???
В Delphi:

Код


ADOQuery1.SQL.text := 'тут текст построенного запроса в виде FIELD1= :FirstName';
ADOQuery1.Parameters.ParamByName('FirstName').Value     := Edit1.Text;
ADOQuery1.Parameters.ParamByName('LastName').Value   := Edit2.Text;
//здесь параметры в зависимости от того же запроса
...
ADOQuery1.Open;

Но есть ли возможность сделать все тонко на стороне сервера?

Itsys

Дата 13.5.2008, 21:34 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

Ну примерно так:

Код


declare @t as varchar(1000)
declare @i as int

declare @first as varchar(1000)
declare @second as varchar(1000)
declare @fird as varchar(1000)
set @first = '1'
set @fird = '3'

set @t = 'SELECT * FROM Table WHERE '
if @first IS NOT NULL
begin
set @t = @t + 'First = ' + @first
set @i = 1;
end
if @second IS NOT NULL
begin
if @i = 1
begin
set @t = @t + ' AND '
end
set @t = @t + 'Second = ' + @second
set @i = 1;
end
if @fird IS NOT NULL
begin
if @i = 1
begin
set @t = @t + ' AND '
end
set @t = @t + 'Fird = ' + @fird
end
print @t
exec(@t)

Juju

Дата 13.5.2008, 21:38 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Оу, спасибо! А как мне теперь это чудо в Delphi показать?

Itsys

Дата 13.5.2008, 21:39 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

C Delphi не знаком... smile

Juju

Дата 13.5.2008, 21:42 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Itsys, все равно спасибо... буду пробовать "прикрутить" это к своему коду...

Juju

Дата 20.5.2008, 19:44 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Делаю по образу и подобию... как показал тов.Itsys, но прикрутить к своему коду так и не удается...

Процедура на Delphi: (передаю один параметр т.к. нет смысла загружать код кучей, если не работает с единственным. Чтобы упростить восприятие)

Код


procedure TfmJOURNAL.BitBtn1Click(Sender: TObject);
begin
 ADODataSet.Close;
 ADODataSet.CommandText := 'MY_PROCEDURE;1';
 ADODataSet.Parameters.Refresh;
 ADODataSet.Parameters[1].Value := SpinEdit.AsInteger;//например 1
 ADODataSet.Open;
end;

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

Код


CREATE PROCEDURE MY_PROCEDURE(
@u nvarchar(100)
) AS
declare @sql as varchar(1000)

set @sql = 'SELECT * FROM TABLE WHERE '

if @u IS NOT NULL
 begin
  set @sql = @sql + 'FIELD_INT = ' + @u
 end

print @sql
exec(@sql)
GO

Генерируется ошибка:
Syntax error converting the nvarchar 'dbo' to a column of data type int

Itsys

Дата 20.5.2008, 20:19 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

Дык у тебя входной параметр nvarchar(100), а поле, судя по названию (FIELD_INT) int, да и 13 строку убери - это я так, в качестве дебаггера использовал smile

либо делай одинаковыми и входной параметр и поле одного типа, либо используй CAST (CONVERT) для преобразования типов.

Juju

Дата 21.5.2008, 18:40 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Itsys, на самом деле все верно... просто я для форума переписывал процедуры, для наглядности чтения... Сейчас приведу полный пример приближенный к боевым - потому как скоро башка выкипит полностью...
Предмет:
ПУСТЬ БУДЕТ:
Таблица 3 поля:

Код


ID int
USER int
DATEWORK datetime --(значения лежат в виде dd.mm.yyyy Например:20.05.2008)

Процедура на Delphi:

Код


 ADODataSet.Close;
 ADODataSet.CommandText := 'SEARCH_JOURNAL;1';
 ADODataSet.Parameters.Refresh;
 //ID пользователя
 ADODataSet.Parameters[1].Value := SpinEdit.AsInteger;
 //Начальный диапазон даты работы
 ADODataSet.Parameters[2].Value := DateEdit1.Date;
 //Конечный диапазон даты работы
 ADODataSet.Parameters[3].Value := DateEdit2.Date;
 ADODataSet.Open;

Процедура на MSSQL2000

Код


CREATE PROCEDURE SEARCH_JOURNAL(
@user int,
@d datetime,
@d2 datetime
) AS
declare @sql as varchar(1000)
declare @i as int
print 'hello'
set @sql = 'SELECT * FROM JOURNAL WHERE '

if @user IS NOT NULL
 begin
  set @sql = @sql + 'USER = ' + CAST(@user as varchar)
  set @i = 1;
 end

if ((@d IS NOT NULL) AND (@d2 IS NOT NULL))
 begin
  if @i = 1
   begin
    set @sql = @sql + ' AND '
   end
  set @sql = @sql + 
'((DATEWORK >=' + CONVERT(char(12),@d,104) + ')AND('+
  'DATEWORK <=' + CONVERT(char(12),@d2,104) + '))'
  set @i = 1;
 end
exec(@sql)
GO

Если не передаются параметры даты, то ошибка та, о которой я писал раньше:
Syntax error converting the nvarchar 'dbo' to a column of data type int
Если передаются параметры даты:
Line 1: Incorrect syntax near '.2008'
С CONVERT я пробовал разные значения style... но ошибка только меняется...

Itsys

Дата 21.5.2008, 22:55 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

У меня вот так все работает:

Код


declare @user as int
declare @d as datetime
declare @d2 as datetime

set @user = 10000
set @d = CONVERT(datetime, '01.01.2008')
set @d2 = CONVERT(datetime, '01.10.2008')

declare @sql as varchar(1000)
declare @i as int
print 'hello'
set @sql = 'SELECT * FROM Goods WHERE '

if @user IS NOT NULL
 begin
  set @sql = @sql + 'GoodId = ' + CAST(@user as varchar)
  set @i = 1;
 end

if ((@d IS NOT NULL) AND (@d2 IS NOT NULL))
 begin
  if @i = 1
   begin
    set @sql = @sql + ' AND '
   end
  set @sql = @sql + 
'((LastStatusChange >=CONVERT(datetime,''' + CONVERT(char(12),@d,104) + ''', 104))AND('+
  'LastStatusChange <=CONVERT(datetime,''' + CONVERT(char(12),@d2,104) + ''', 104)))'
  set @i = 1;
 end
print(@sql)
exec(@sql)

ЗЫ Названия таблиц мои

Juju

Дата 22.5.2008, 17:44 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 51
Регистрация: 26.10.2006

Репутация: нет
Всего: 1

Itsys, во-первых спасибо огромное, что возишься с моей проблемой
во-вторых СПАСИБЕЩЕ ТЕБЕ ОГРОМНОЕ за подсказку с конвертированием дат - все работает...

Но вот беда.... при:

Код


set @sql = 'SELECT * FROM Goods WHERE '

if @user IS NOT NULL
 begin
  set @sql = @sql + 'GoodId = ' + CAST(@user as varchar)
  set @i = 1;
 end

все равно выдает мне:
Syntax error converting the nvarchar 'dbo' to a column of data type int

Itsys

Дата 22.5.2008, 19:11 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

у меня при пустых параметрах @d и @d2 тоже все работает правильно - значит проблема в том, что передается в параметр @user выведи итоговый запрос в print и посмотри, что там получается
У меня получается

Код


SELECT * FROM Goods WHERE GoodId = 1000

Может у тебя чего-нибудь такое (судя по ошибке)

Код


SELECT * FROM Goods WHERE GoodId = dbo

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MS SQL Server \| Следующая тема »