Необязательные параметры в хранимой процедуре SQL

Я пытаюсь создать хранимую процедуру с необязательными параметрами. Я следовал приведенным ниже инструкциям здесь. Я также сослался на этот вопрос SO . Однако я продолжаю получать следующую ошибку:

Ошибка преобразования типа данных varchar в int.

Он работает, когда я выполняю его как

EXEC sp_get_user {id#}

or 

EXEC sp_get_user NULL, {username}

но терпит неудачу с

EXEC sp_get_user {username}

Хранимая процедура

@id int = NULL,
@username nvarchar(50) = NULL

SELECT
    username = COALESCE(a.Username, b.Username),
    password = COALESCE(a.Password, b.Password),
    signup_date = COALESCE(a.SignedUpOn, b.Signup_Date)
FROM table1 a
FULL OUTER JOIN table 2 b
ON a.ID = b.ID
WHERE ((a.ID = @id OR @id IS NULL)
AND (a.Username = @username OR @username IS NULL)
OR (b.ID = @id OR @id IS NULL)
AND (b.Username = @username OR @username IS NULL))

Я пытался добавить OPTION(RECOMPILE) и не имел успеха. Я хочу сделать это динамическим, чтобы другие разработчики могли вызывать этот SP без необходимости каждый раз указывать все параметры. Они будут подключаться через LINQ, если это имеет значение.


stored-procedures sql-server-2008 linq optional-parameters
person jon3laze    schedule 07.04.2011    source источник
comment
Старый вопрос, найденный через Google - почему это помечено как LINQ? подключение через LINQ — единственная ссылка на LINQ   -  person Dan    schedule 23.07.2014

Ответы (3)


arrow_upward
5
arrow_downward

используйте именованные параметры в этом случае

EXEC sp_get_user @username = {username}

если оба параметра являются необязательными, SQL-сервер будет идти по позиции, поэтому первый параметр, который вы передаете, будет сопоставлен с первым в процедуре.

person SQLMenace    schedule 07.04.2011

arrow_upward
4
arrow_downward

При выполнении хранимых процедур вы должны соответствовать порядку параметров, определенному соответственно, поэтому первый и второй операторы работают нормально, в первом EXEC sp_get_user {id#} вы передали идентификатор и проигнорировали имя пользователя, затем он принимает определенное значение по умолчанию. Более того, во втором операторе EXEC sp_get_user NULL, {username} вы указали NULL для идентификатора и передали значение для параметра имени пользователя, поэтому он также работает.

С другой стороны, третий EXEC sp_get_user {username} не работает, потому что SQL Server обработал ваш параметр {username} как значение id, поэтому он пытается преобразовать его в целое число, и, конечно, это не удастся. Вместо этого вы должны указать имя параметра при передаче его значения, см. следующий код:

EXEC sp_get_user @username = {username}
person Mohammed A. Fadil    schedule 07.04.2011

arrow_upward
3
arrow_downward

Ну да, очевидно, что ваша последняя попытка потерпит неудачу.

Ваш хранимый процесс ожидает два параметра в таком порядке:

  1. @id INT
  2. @username NVARCHAR(50)

Если вы просто вызовете свою хранимую процедуру с одним параметром, то этот параметр будет сопоставлен с @id, и поэтому он должен быть INT.

Если вы хотите вызвать сохраненный процесс только с одним значением имени пользователя, вам нужно будет использовать параметр named — вы не можете полагаться на позицию (поскольку первый и единственный параметр всегда будет соответствовать @id)

EXEC sp_get_user @username = {username}
person marc_s    schedule 07.04.2011