Olá Daniel!!!
Abaixo segue partes de uma resposta que dei a outro post aqui no fórum (sobre o pq de usar SPs), depois colocarei um exemplo de SP.
"A principal vantagem das SPs é a performance. Elas são otimizadas para manter cache dos planos de execução, o que já faz uma grande diferença.
Depois, a administração da lógica/regras de negócio fica centralizada.
A GRANDE recomendação (é quase uma lei, hehe), é manter as SPs sempre o mais curtas possíveis. Uma frase que ouvi uma vez, as SPs devem ser pequenas e atômicas, ou seja, muito rápidas.
Depois disso, evitar a criação de objetos dentro das SPs, tentar usar sempre variáveis. EVITAR desvios de código (os benditos IFs...), isso acaba invalidando os planos de execução.
Complementanto...
O plano de execução é tipo um mapa de como o SQL irá executar a operação. Imagine que você tem uma consulta com join entre 5 tabelas e mais 3 parâmetros na clausula where. O SQL terá que encontrar qual a melhor maneira de executar essa consulta, por onde ele começa a fazer a associação das tabelas e quais índices utilizar para isso. Todas essas informações ficam dentro do plano de execução. Uma vez criado, ele vai para o cache, e pode ser reutilizado na próxima vez que o procedimento for executado.
O processo de criação do plano de execução pode ser um tanto pesado, dependendo da quantidade de informações, tabelas, índices, parâmetros, etc. Então, o cache vai permitir que não seja necessário executar a criação do plano, que já existe.
Sobre a "atomicidade", dê preferência a procedures que executem apenas uma tarefa. Por exemplo, se tiver uma aplicação com cadastro de clientes, criar uma procedute de insert do cliente, uma de update, uma de delete e uma de consulta. Tente evitar ao máximo a utilização de IFs, se não tiver como escapar, crie uma proc principal com os IFs e ao invés de criar ter as outras instruções nessa mesma proc, crie outras procs apenas com as instruções executadas dentro do IF. "
Exemplo de procedure:
Code Snippet
-- CRIAÇÃO DE TABELA PARA EXEMPLO
CREATE TABLE TESTE (ID INT IDENTITY(1,1), NOME VARCHAR (100))
-- INSERT DE DADOS
INSERT INTO TESTE (NOME) VALUES ('JOÃO')
INSERT INTO TESTE (NOME) VALUES ('MARIA')
-- SP DE CONSULTA POR NOME
CREATE PROC dbo.spuConsulta
@INICIAL VARCHAR (20)
AS
SELECT * FROM TESTE WHERE NOME LIKE @INICIAL
GO
Bom, a chamada da SP usando parâmetros, vais ter que esperar a resposta de outro colega, pois não sou programador.
Espero ter ajudado.
Grande abraço
