sp_executesql : How to use ?

				
--**************************************
-- 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
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1435&lngWId=5--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;)
-- **************************************************************************