UNKNOWN --************************************** -- Name: sp_executesql : How to use ? -- Description:sp_executesql exemplo example Como usar a sp_executesql ? How to use the sp_executesql ? -- By: Francisco Antonio de Araujo e Souza -- -- -- Inputs:None -- -- Returns:None -- --Assumes:None -- --Side Effects:None --This code is copyrighted and has limited warranties. --Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.1435/lngWId.5/qx/vb/scripts/ShowCode.htm --for details. --************************************** -- ************************************************************************** -- Como usar a sp_executesql ? -- How to use the sp_executesql ? -- By Prof. Dr. Francisco Antonio de Araújo e Souza -- Fortaleza - Ceará - Brasil -- 07 de Julho de 2013 -- ************************************************************************** -- ************************************************************************** -- -- ESTRUTURA DO BANCO DE DADOS E DA TABELA DE EXEMPLO: -- --BD_ALUNOS -- TB_CADASTRO -- CP_MATRICULAint -- CP_NOME varchar(30) -- -- DADOS DA TABELA TB-CADASTRO: -- ------------------------------------------------ --| CP_MATRICULA | CP_NOME| --|-------------------------|-------------------| --|1 | ANTONIO| --|2 | BEATRIZ | --|3 | CARLOS | ------------------------------------------------ -- -- ************************************************************************** -- ************************************************************************** -- -- Consultando a tabela de forma normal -- USE BD_ALUNOS DECLARE @Dado int; SET @Dado = 2 SELECT CP_NOME AS ALUNOS FROM TB_CADASTRO WHERE CP_MATRICULA = @Dado; -- ************************************************************************** -- ************************************************************************** -- -- Agora fazendo a mesma consulta através da sp_executesql -- USE BD_ALUNOS DECLARE @Dado int; DECLARE @SQL nvarchar(500); DECLARE @Parmetronvarchar(500); DECLARE @Nomevarchar(30); SET @Dado = 2; SET @SQL = N'SELECT @NomeOUT = CP_NOME FROM TB_CADASTRO WHERE CP_MATRICULA = @Mat'; SET @Parmetro = N'@Mat int, @NomeOUT varchar(30) OUTPUT'; EXECUTE sp_executesql @SQL, @Parmetro, @Mat = @Dado, @NomeOUT=@Nome OUTPUT; SELECT @Nome AS ALUNOS; -- (OU PRINT @Nome;) -- **************************************************************************