Partilhar via

  • Artigo

Executando procedimentos armazenados (Mecanismo de Banco de Dados)

Para executar um procedimento armazenado, use a instrução Transact-SQL EXECUTE. Como alternativa, não será preciso executar o procedimento armazenado usando a palavra-chave EXECUTE se o procedimento armazenado for a primeira instrução do lote.

Executando procedimentos armazenados do sistema

Procedimentos armazenados do sistema começam com os caracteres sp_. Fisicamente, eles são armazenados no Banco de dados de recurso, porém, obviamente, aparecem no esquema sys de todos os banco de dados do sistema e dos bancos de dados definidos pelo usuário na instância do SQL Server. Os procedimentos armazenados do sistema podem ser executados de qualquer banco de dados sem ter a total qualidade do nome do procedimento armazenado. Um nome não qualificado pelo esquema é um nome de uma parte como sp_someproc ou um nome de três partes como somedb..sp_someproc, sendo que a segunda parte, o nome do esquema, não é especificado.

Recomendamos que você qualifique por esquema todos os nomes dos procedimentos armazenados do sistema com o nome do esquema sys para evitar conflitos de nomes. O exemplo a seguir mostra o método recomendado para executar um procedimento armazenado do sistema.

EXEC sys.sp_who;

Os exemplos a seguir demonstram métodos compatíveis com versões anteriores para executar procedimentos armazenados do sistema.

ObservaçãoObservação

Os seguintes métodos de execução dos procedimentos armazenados do sistema serão removidos em versões futuras do SQL Server. Evite usar esses métodos em novo projeto de desenvolvimento e planeje modificar os aplicativos que os utilizam atualmente.

 
EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;

Agrupamento de banco de dados correspondente

SQL Server 2008 usa o agrupamento de banco de dados de chamada quando corresponde aos nomes dos procedimentos do sistema. Portanto, em seu aplicativo, você deve sempre fazer a diferenciação exata entre maiúsculas e minúsculas nos nomes dos procedimentos do sistema. Por exemplo, este código falhará caso seja executado no contexto de um banco de dados que tenha um agrupamento com diferenciação de maiúsculas e minúsculas:

exec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help

Use os sys.system_objects e a exibição dos catálogos sys.system_parameters para exibir os nomes exatos dos procedimentos armazenados do sistema.

Executando os procedimentos armazenados estendidos de sistema

Os procedimentos armazenados estendidos de sistema começam com os caracteres xp_. Fisicamente, eles são armazenados no Banco de dados de recurso, porém, obviamente, aparecem no esquema sys de todo o sistema, e o banco de dados definido pelo usuário na instância do SQL Server. O exemplo a seguir mostra o método recomendado para executar um procedimento armazenado estendido do sistema.

EXEC sys.xp_subdirs 'c:\';

Executando procedimentos armazenados definidos pelo usuário

Ao executar um procedimento definido pelo usuário (em um lote ou dentro de um módulo, como a função ou o procedimento armazenado definido pelo usuário), é extremamente recomendável qualificar o nome do procedimento armazenado com, no mínimo, o nome do esquema.

O exemplo a seguir mostra o método recomendado para executar um procedimento armazenado definido pelo usuário.

USE AdventureWorks2008R2;
GO
EXEC dbo.uspGetEmployeeManagers 50;

-Ou-

EXEC AdventureWorks2008R2.dbo.uspGetEmployeeManagers 50;
GO

Se um procedimento armazenado não qualificado definido pelo usuário for especificado, o Mecanismo de Banco de Dados pesquisará o procedimento na seguinte ordem:

  • Esquema sys do banco de dados atual.

  • O esquema padrão do chamador se executado em um lote ou em SQL dinâmico. Ou, se o nome do procedimento não qualificado aparecer no corpo da definição de outro procedimento, o esquema com esse outro procedimento será pesquisado a seguir. Para obter mais informações sobre esquemas padrão, consulte Separação do Esquema de Usuário.

  • O esquema dbo no banco de dados atual.

Observação importanteImportante

Se qualquer procedimento armazenado criado pelo usuário tiver o mesmo nome de um procedimento armazenado do sistema, o procedimento armazenado criado pelo usuário nunca será executado se você usar uma referência do nome não qualificado pelo esquema. Para obter mais informações, consulte Criando procedimentos armazenados (Mecanismo de Banco de Dados).

Especificando parâmetros

Os valores dos parâmetros podem ser fornecidos se um procedimento armazenado for gravado para aceitá-los.

O valor fornecido deve ser uma constante ou uma variável; não é possível especificar um nome de função como valor de parâmetro. As variáveis podem ser definidas pelo usuário ou pelas variáveis de sistema como @@spid.

Os exemplos a seguir mostram os valores de parâmetro passando para o procedimento armazenado uspGetWhereUsedProductID. O procedimento espera valores para dois parâmetros de entrada: uma ID de produto e uma data. Os exemplos mostram como passar os parâmetros como constantes e variáveis e também como usar uma variável para passar o valor de uma função.

USE AdventureWorks2008R2;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

Caso queira especificar os parâmetros em uma ordem diferente daquela em que estão definidos no procedimento armazenado, você deve nomeá-los. Para obter mais informações, consulte Especificando um nome de parâmetro.

Para especificar que um parâmetro deve retornar um valor para o programa de chamada, use a palavra-chave OUTPUT. Para obter mais informações, consulte Especificando a direção de um parâmetro.

Especificando a ordem dos parâmetros

Se você fornecer parâmetros ao formulário **@parâmetro =**value, você poderá fornecê-los em qualquer ordem. Você também pode omitir parâmetros para os quais foram fornecidos padrões. Se você fornecer um parâmetro no formulário **@parâmetro =**value, você deve fornecer todos os parâmetros subsequentes desse modo. Se você não fornecer um parâmetro ao formulário **@parâmetro =**value, você deve fornecê-los na ordem dada presente na instrução CREATE PROCEDURE.

Ao executar um procedimento armazenado, o servidor rejeita qualquer parâmetro que não tenha sido incluído na lista de parâmetros durante o procedimento de criação. Qualquer parâmetro passado através da referência (explicitamente passando o nome do parâmetro) não será aceito se o nome do parâmetro não coincidir.

Usando valores padrão em parâmetros

Embora você possa omitir os parâmetros para os quais foram fornecidos padrões, somente a lista de parâmetros poderá ser truncada. Por exemplo, se um procedimento armazenado tiver cinco parâmetros, você poderá omitir o quarto e o quinto parâmetro, mas não poderá pular o quarto e ainda incluir o quinto, a menos que forneça os parâmetros no formulário **@parameter =**value.

O valor padrão de um parâmetro, se definido para o parâmetro no procedimento armazenado, é usado quando:

  • Nenhum valor para o parâmetro for especificado quando o procedimento armazenado estiver sendo executado.

  • A palavra-chave DEFAULT for especificada como o valor para o parâmetro.