Как составить запрос в хранимой процедуре с возможностью передачи NULL параметров

@Casper-SC · Регистрация: 27.03.2010

Author24 — интернет-сервис помощи студентам

Есть хранимая процедура, которая не работает как надо. Она используется для поиска данных в базе по переданным параметрам.

T-SQL

ALTER PROCEDURE [dbo].[SearchDebitors](@Name nvarchar(50), @PostNumber int, @PhoneNumber nchar(30))
 
AS
    SELECT * FROM Debitors WHERE 
    [Name] LIKE '%' + @Name + '%' AND 
    CAST (PostNumber AS nvarchar(11)) LIKE '%' + CAST(@PostNumber AS nvarchar(11)) + '%' 
    AND CAST (PhoneNumber AS nvarchar) LIKE '%' + CAST (@PhoneNumber AS nvarchar) + '%'  
           OR PhoneNumber IS NULL
    RETURN

Я хочу сделать что-то типа того, что я сделал в коде в C#, но передача запроса это вроде как не безопасно, хочу переделать всё в хранимые процедуры.
Это метод класса (вызываю хранимую процедуру я не этим методом, это просто пример того как я строю запрос без хранимой процедуры):

C#

        internal List<DbDataRecord> SearchDebitors(string name, int? postNumber, string phoneNumber)
        {
            bool nameIsFilled = !string.IsNullOrWhiteSpace(name);
            bool postNumberIsFilled = postNumber != null;
            bool phoneNumberIsFilled = !string.IsNullOrWhiteSpace(phoneNumber);
            if (!nameIsFilled && !postNumberIsFilled && !phoneNumberIsFilled)
                return null;
 
            StringBuilder sbCommandText = new StringBuilder("SELECT * FROM Debitors");
            SqlCommand command = new SqlCommand();
            int lastLength = sbCommandText.Length;
            if (nameIsFilled)
            {
                command.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar) { Value = name });
                sbCommandText.Append(" WHERE [Name] LIKE '%' + @Name + '%'");
            }
            if (postNumberIsFilled)
            {
                if (lastLength != sbCommandText.Length)
                    sbCommandText.Append(" AND ");
                else
                    sbCommandText.Append(" WHERE ");
                command.Parameters.Add(new SqlParameter("@PostNumber", SqlDbType.Int) { Value = (int)postNumber });
                sbCommandText.Append("CAST (PostNumber AS nvarchar(11)) LIKE '%' + CAST(@PostNumber AS nvarchar(11)) + '%'");
            }
            if (phoneNumberIsFilled)
            {
                if (lastLength != sbCommandText.Length)
                    sbCommandText.Append(" AND ");
                else
                    sbCommandText.Append(" WHERE ");
                command.Parameters.Add(new SqlParameter("@PhoneNumber", SqlDbType.NChar) { Value = phoneNumber, IsNullable = true });
                sbCommandText.Append("PhoneNumber LIKE '%' + @PhoneNumber + '%'");
            }
 
            command.CommandText = sbCommandText.ToString();
            return GetData(command);
        }

После выполнения этого кода, в случае, если передали все 3 параметра, мы получим запрос:

T-SQL

SELECT * FROM Debitors WHERE 
[Name] LIKE '%' + @Name + '%' AND 
CAST (PostNumber AS nvarchar(11)) LIKE '%' + CAST(@PostNumber AS nvarchar(11)) + '%' AND 
PhoneNumber LIKE '%' + @PhoneNumber + '%'

Если не передадут, скажем, PostNumber, то получим:

T-SQL

1
2
3

SELECT * FROM Debitors WHERE 
[Name] LIKE '%' + @Name + '%' AND 
PhoneNumber LIKE '%' + @PhoneNumber + '%'

Ну и так далее. А как вообще дать возможность в хранимую процедуру передавать NULL значения? И как потом в процедуре построить такую логику, типа, если какой-то параметр NULL, то он не участвует в условии после WHERE.

@iap · 17.08.2012, 16:13

Сообщение от Casper-SC

Если не передадут, скажем, PostNumber, то получим:

T-SQL

1
2
3

SELECT * FROM Debitors WHERE 
[Name] LIKE '%' + @Name + '%' AND 
PhoneNumber LIKE '%' + @PhoneNumber + '%'

Да?! Интересно.
Вообще-то, получим

T-SQL

[Name] LIKE '%'+@Name+'%'
AND CAST (PostNumber AS nvarchar(11)) LIKE '%'+NULL+'%'
AND CAST (PhoneNumber AS nvarchar) LIKE '%'+CAST(@PhoneNumber AS nvarchar)+'%'
OR PhoneNumber IS NULL

Это вернёт TRUE только если PhoneNumber IS NULL,
ибо из-за NULL второе условие вернёт UNKNOWN.
Это если не установлен SET CONCAT_NULL_YIELDS_NULL OFF
Но в ближайшей версии сервера всегда будет ON. И сейчас рекомендуется ON

T-SQL

SELECT *
FROM Debitors
WHERE (@Name IS NULL OR [Name]=@Name)
  AND PostNumber BETWEEN ISNULL(@PostNumber,-2147483648) AND ISNULL(@PostNumber,2147483647)
  AND (@PhoneNumber IS NULL OR PhoneNumber=@PhoneNumber);

@Casper-SC · 17.08.2012, 18:23 **[ТС]**

Сообщение от iap

Да?! Интересно.
Вообще-то, получимКод T-SQL

T-SQL

[Name] LIKE '%'+@Name+'%'
AND CAST (PostNumber AS nvarchar(11)) LIKE '%'+NULL+'%'
AND CAST (PhoneNumber AS nvarchar) LIKE '%'+CAST(@PhoneNumber AS nvarchar)+'%'
OR PhoneNumber IS NULL

Я говорил про код C#.

Добавлено через 1 час 4 минуты
Не работает код, который вы дали. Не работает так, как мне нужно.

Есть 3 строки поиска в программе "Имя", "Почтовый индекс", "Номер телефона". Человек вводит в поиск данные и программа отправляет запрос хранимой процедуре, но реально можно что-то найти таким поиском, если введены все 3 параметра поиска, а пользователь может ввести их в любой комбинации. При том номер телефона в базе данных может быть NULL, всё остальное не может. Я уже замучался составлять этот запрос, так и не составил.

@Casper-SC · 17.08.2012, 18:45 **[ТС]**

Если запрос составлять в коде C# и отправлять его напрямую без хранимой процедуры, то я могу составлять любые комбинации этого запроса, которые я выше показывал, а вы не поняли меня. А вот в хранимой процедуре как описать не знаю.

Добавлено через 18 минут
Если "Имя" равно пустая строка, то не учитывать вообще имя в запросе SELECT * FROM, если "почтовый индекс" равен пустой строке, то не учитывать и его. Ну это примерно так. Пускай вообще нельзя передавать параметры NULL, если передана пустая строка, то не учитывать её в условии поиска. Вот как это сделать?

@Аватар · 17.08.2012, 22:24

Можно в той же процедуре не загромождать WHERE анализами на NULL и OR-ами, а сформировать условие и сам запрос динамически и выполнить EXEC (@s)

@Casper-SC · 18.08.2012, 10:54 **[ТС]**

Сообщение от Аватар

Можно в той же процедуре не загромождать WHERE анализами на NULL и OR-ами, а сформировать условие и сам запрос динамически и выполнить EXEC (@s)

Может и можно, но чувствую, чтобы я это мог делать сам, мне надо недели 2 сидеть книгук читать и примеры с неё делать, а я пока учу вообще работу с базами данных на C#. T-SQL ещё успеется, там в C# вообще столько всего, что можно учить не один год.

@Аватар · 19.08.2012, 12:08

Может и можно

Примерно в этом направлении

T-SQL

ALTER PROCEDURE [dbo].[SearchDebitors](@Name nvarchar(50), @PostNumber int, @PhoneNumber nchar(30)) AS
BEGIN
  DECLARE @s nvarchar(2000)
  SET @s=''
  IF @Name IS NOT NULL SET @s=@s+'[Name]='''+@Name+''' AND '
  IF @PostNumber IS NOT NULL SET @s=@s+'PostNumber='+CAST(@PostNumber AS nvarchar)+' AND '
  IF @PhoneNumber IS NOT NULL SET @s=@s+'PhoneNumber='''+CAST(@PhoneNumber AS nvarchar)+''' AND '
  IF @s<>'' SET @s=' WHERE '+LEFT(@s,LEN(@s)-5)
  SET @s='SELECT * FROM Debitors'+@s
  EXEC (@s) 
END

@Casper-SC 4432 / 2092 / 404 Регистрация: 27.03.2010 Сообщений: 5,657 Записей в блоге: 1
	17.08.2012, 18:45 [ТС]	4
	Если запрос составлять в коде C# и отправлять его напрямую без хранимой процедуры, то я могу составлять любые комбинации этого запроса, которые я выше показывал, а вы не поняли меня. А вот в хранимой процедуре как описать не знаю. Добавлено через 18 минут Если "Имя" равно пустая строка, то не учитывать вообще имя в запросе SELECT * FROM, если "почтовый индекс" равен пустой строке, то не учитывать и его. Ну это примерно так. Пускай вообще нельзя передавать параметры NULL, если передана пустая строка, то не учитывать её в условии поиска. Вот как это сделать? 0

@Аватар 3394 / 1314 / 469 Регистрация: 31.05.2012 Сообщений: 4,653
	17.08.2012, 22:24	5
	Можно в той же процедуре не загромождать WHERE анализами на NULL и OR-ами, а сформировать условие и сам запрос динамически и выполнить EXEC (@s) 0

@Casper-SC 4432 / 2092 / 404 Регистрация: 27.03.2010 Сообщений: 5,657 Записей в блоге: 1
	18.08.2012, 10:54 [ТС]	6
	Сообщение от Аватар Можно в той же процедуре не загромождать WHERE анализами на NULL и OR-ами, а сформировать условие и сам запрос динамически и выполнить EXEC (@s) Может и можно, но чувствую, чтобы я это мог делать сам, мне надо недели 2 сидеть книгук читать и примеры с неё делать, а я пока учу вообще работу с базами данных на C#. T-SQL ещё успеется, там в C# вообще столько всего, что можно учить не один год. 0

	19.08.2012, 12:08