Главная » Microsoft SQL Server, Базы данных » Передача данных в хранимые процедуры

0

Хранимая процедура становится только более ценной, если использует параметры. Ранее созданная хранимая процедура CategoryList возвращает названия всех категорий товаров. В то же время процедуре, извлекающей конкретную строку таблицы, нужно передавать некоторый параметр (например, идентификатор товара).

Хранимые процедуры SQL Server могут иметь массу входных и выходных параметров (точнее говоря, вплоть до 2100 единиц).

Входные параметры

В инструкции CREATE PROCEDURE можно перечислить параметры, передаваемые хранимой процедуре, указав их непосредственно после ее имени. Каждый из параметров должен начинаться с символа @. Для хранимой процедуры он является локальной переменной. Как и все локальные переменные, параметры должны объявляться с допустимыми типами данных. При вызове хранимой процедуры должны также указываться все параметры (если, конечно, некоторые из них не имеют значений, заданных по умолчанию).

В следующем примере создается хранимая процедура, возвращающая одну категорию товаров. Параметр @CategoryName может принимать текстовую строку в таблице Unicode длиной до 35 символов. Передаваемое значение в самой хранимой процедуре присваивается локальной переменной и используется в предложении WHERE:

USE OBXKites;

go

CREATE PROCEDURE CategoryGet

(@CategoryName NVARCHAR(35))

AS

SELECT ProductCategoryName, ProductCategoryDescription FROM dbo.ProductCategory

WHERE ProductCategoryName = @CategoryName ,*

При выполнении следующего программного кода хранимой процедуре в качестве параметра передается литерал ‘Kite’, который подставляется в качестве локальной переменной в предложение WHERE запроса SELECT:

EXEC CategoryGet 1 Kite';

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

ProductCategoryName ProductCategoryDescription

Kite                               a variety of kites, from simple

stunt, to Chinese, to novelty

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

В следующих трех примерах продемонстрированы вызовы хранимых процедур и передача им параметров с использованием исходного порядка и имен:

EXEC StoredProcedure @Parameterl = п,

@Parameter2 = ‘л1;

EXEC StoredProcedure п, ‘n';

EXEC StoredProcedure п, @Parameter2 = ‘п';

Значения параметров, заданные по умолчанию

При вызове хранимой процедуры вы обязаны указать все ее параметры, кроме тех, для которых были определены значения по умолчанию. В определении хранимой процедуры значения по умолчанию определяются добавлением к имени параметра знака равенства и значения: CREATE PROCEDURE имя_процедуры (

@переменная тип_данных = значение_по_умолчанию ) ;

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

CREATE PROCEDURE pProductCategory_Fetch2(

©Search NVARCHAR(50} = NULL

)

– Если ©Search = null, возвращаются все категории товаров — Если в ©Search передается значение, фильтруются названия AS

SET NOCOUNT ON;

SELECT ProductCategoryName, ProductCategoryDescription FROM dbo.ProductCategory WHERE ProductCategoryName = @Search OR @Search IS NULL;

IF @@RowCount = 0 RAISERROR(

‘Категория ”%s” не найдена. ‘ ,14,1,@Search);

В первом вызове мы передадим хранимой процедуре имя категории:

EXEC pProductCategory_Fetch ‘OBX';

Будет получен следующий результат:

ProductCategoryName ProductCategoryDescription

OBX                               OBX stuff

Если процедура pProductCategory_Fetch выполняется без явного указания параметра, то переменной @Search присваивается значение по умолчанию — null, что делает истинным выполнение условии предложения WHERE для всех строк таблицы ProductCategory.

EXEC pProductCategory_Fetch;

ProductCategoryName ProductCategoryDescription

Accessory                       kite flying accessories

Book                               Outer Banks books

Clothing                       OBX t-shirts, hats, jackets

Kite                               a variety of kites, from simple

stunt, to Chinese, to novelty Material Kite construction material

OBX                            OBX stuff

Toy                               Kids stuff

Video                            stunt kite contexts and lessons,

and Outer Banks videos

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии