Поделиться через

  • Статья

Указание значения параметра по умолчанию

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

Значения по умолчанию необходимы, так как при их отсутствии выполнение хранимой процедуры вызывает ошибку, когда в вызывающей программе параметры не определены.

Если же значение параметра по умолчанию не может быть корректно задано, то необходимо указать значение NULL, а также создать для хранимой процедуры специальное сообщение, которое будет выдаваться при ее выполнении с неопределенным параметром.

ПримечаниеПримечание

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

Примеры

В следующем примере создается процедура usp_GetSalesYTD с единственным входным параметром @SalesPerson. В качестве значения по умолчанию параметру присваивается NULL, которое используется в инструкциях обработки ошибок для выдачи сообщения, если хранимая процедура выполняется с неопределенным параметром @SalesPerson.

USE AdventureWorks;
GO
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL  -- NULL default value
AS 
    SET NOCOUNT ON; 

-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
   PRINT 'ERROR: You must specify the last name of the sales person.'
   RETURN
END
-- Get the sales for the specified sales person and 
-- assign it to the output parameter.
SELECT SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID
WHERE LastName = @SalesPerson;
RETURN
GO

Следующий пример выполняет хранимую процедуру. Первая инструкция выполняет хранимую процедуру без указания входного значения. В результате чего инструкции обработки ошибок хранимой процедуры возвращают пользовательское сообщение об ошибке. Вторая инструкция задает входное значение и возвращает ожидаемый результирующий набор.

-- Run the stored procedure without specifying an input value.
EXEC Sales.usp_GetSalesYTD;
GO
-- Run the stored procedure with an input value.
EXEC Sales.usp_GetSalesYTD N'Blythe';
GO

Следующий пример показывает использование процедуры my_proc со значениями по умолчанию для всех трех параметров @first, @second и @third, а также выходные данные хранимой процедуры, выполненной с другими значениями параметров:

IF OBJECT_ID('dbo.my_proc', 'P') IS NOT NULL
    DROP PROCEDURE dbo.my_proc;
GO
CREATE PROCEDURE dbo.my_proc
    @first int = NULL,  -- NULL default value
    @second int = 2,    -- Default value of 2
    @third int = 3      -- Default value of 3
AS 
    SET NOCOUNT ON;
    SELECT @first, @second, @third;
GO

EXECUTE dbo.my_proc; -- No parameters supplied
GO

Ниже приводится результирующий набор.

NULL  2  3

EXECUTE dbo.my_proc 10, 20, 30;-- All parameters supplied
GO

Ниже приводится результирующий набор.

10  20  30

EXECUTE dbo.my_proc @second = 500;  -- Only second parameter supplied by name
GO

Ниже приводится результирующий набор.

NULL  500  3

EXECUTE dbo.my_proc 40, @third = 50 -- Only first and third parameters
                                    -- are supplied.

Ниже приводится результирующий набор.

40  2  50