STORED PROCEDURE - SqlServer

[Sub-Zero]

HELLO WORLD

Código:

CREATE PROCEDURE TESTE
AS
BEGIN
SELECT ‘O FAMOSO HELLO WORLD!’
END

MULTIPLICACAO POR PARAMETRO

Código:

CREATE PROCEDURE TESTE @PAR1 INT
AS
BEGIN
SELECT @PAR1 * @PAR1 AS QUADRADO
END

SELECAO POR PARAMETRO

Código:

CREATE PROCEDURE TESTE @PAR1 VARCHAR(5)
AS
BEGIN
SELECT CLIE_COD , CLIE_NOM FROM CADCLIE
WHERE CLIE_COD = @PAR1
END

ALTERAR NOME CLIENTE

Código:

CREATE PROCEDURE TESTE @PAR1 VARCHAR(5)
AS
BEGIN
UPDATE CADCLIE SET CLIE_NOM = 'ALTEREI O NOME DO CLIENTE'
WHERE CLIE_COD = @PAR1
END

STORE PROCEDURE COM LOOP

Código:


USE pubs
GO
WHILE (SELECT AVG(Preco) FROM Livros) 30
BEGIN
UPDATE Livros
SET preco = preco * 2
SELECT MAX(preco) FROM Livros
IF (SELECT MAX(preco) FROM Livros) 50
BREAK
ELSE
CONTINUE
END

STORE PROCEDURE COM LIKE

Código:


CREATE PROCEDURE sp_CEP_log_SPAULO
@NOME_CEP AS VARCHAR(8)
AS
SELECT * FROM C_TB_BCEP_LOGR_SP
WHERE Logradouro LIKE @NOME_CEP + '%'
GO


STORE PROCEDURE COMPLEMENTAR

Código:


CREATE PROCEDURE completar (nome varchar(255))
BEGIN
select cli_nome from tt_clientes where cli_nome like nome;
END;


STORED PROCEDURE COM RETORNO DE VALORES

Código:


CREATE PROCEDURE sp1001_TravarCODIGOITEMEdicao
@CODIGODEPARTAMENTO FLOAT = 0,
@CODIGOITEM FLOAT = 0,
@CODIGOSEGMENTO varChar(2) = null,
@LOGIN varChar(7) = null,
@RESULTADO int OUTPUT
AS
DECLARE @AGORA varChar(20)

SET @AGORA = Convert(VarChar(20),GetDate(),103) + ' ' + Convert(VarChar(20),GetDate(),108)

IF EXISTS(SELECT * From Tbl_Sistema_TravaEdicao Where CODIGOSEGMENTO=@CODIGOSEGMENTO AND CODIGOITEM=@CODIGOITEM AND CODIGODEPARTAMENTO=@CODIGODEPARTAMENTO AND Login=@Login)
BEGIN
DELETE FROM Tbl_Sistema_TravaEdicao Where CODIGOSEGMENTO=@CODIGOSEGMENTO AND CODIGOITEM=@CODIGOITEM AND CODIGODEPARTAMENTO=@CODIGODEPARTAMENTO AND Login=@Login
set @RESULTADO = 0
END
ELSE
BEGIN
IF EXISTS(SELECT * From Tbl_Sistema_TravaEdicao Where CODIGOSEGMENTO=@CODIGOSEGMENTO AND CODIGOITEM=@CODIGOITEM AND CODIGODEPARTAMENTO=@CODIGODEPARTAMENTO AND Login<>@LOGIN)
BEGIN
set @RESULTADO = -1
END
ELSE
BEGIN
INSERT INTO Tbl_Sistema_TravaEdicao (CODIGODEPARTAMENTO,CODIGOITEM,CODIGOSEGMENTO,LOGIN,DTREGISTRO) values (@CODIGODEPARTAMENTO,@CODIGOITEM,@CODIGOSEGMENTO,@LOGIN,@AGORA)
set @RESULTADO = -2
END
END
GO


ENVIANDO E-MAIL VIA STORED PROCEDURE

Código:


CREATE Procedure sn_SMTPMail
/*********************************************************************
** Lavanya Simon
**
** PURPOSE:
** This stored procedure is used to send email from MS SQL Database
**
** TABLE USED:
** TEMP
** RESULT:
** Email Sent.
** Command:
** exec sn_SMTPMail
**********************************************************************/
AS
SET nocount on
declare @oMail int
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
BEGIN
DECLARE @t int
DECLARE cur CURSOR FOR Select id from temp where checkrecord = 'Y' and termdate > getdate() - 10
OPEN cur
FETCH NEXT FROM cur INTO @t
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', 'lavanyasimonyahoo.com'
EXEC @resultcode = sp_OASetProperty @oMail, 'To', 'simonnadaryahoo.com'
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @t
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', 'ID Expires in next 10 days. Please Take Appropriate Action'
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
FETCH NEXT FROM cur INTO @t
END
CLOSE cur
DEALLOCATE cur
END
SET nocount off
GO

CHAMAR UMA STORED PROCEDURE DENTRO DE OUTRA

Código:


CREATE PROCEDURE sp_1
AS

SET NOCOUNT ON
CREATE TABLE #Teste
(
Campo01 INT,
Campo02 VARCHAR(15)
)

INSERT INTO #Teste(Campo01, Campo02)
VALUES (1,'Valor 1 - SP_1')
INSERT INTO #Teste(Campo01, Campo02)
VALUES (2,'Valor 2 - SP_1')
INSERT INTO #Teste(Campo01, Campo02)
VALUES (3,'Valor 3 - SP_1')

PRINT 'ANTES DE EXECUTAR sp_2'
SELECT * FROM #Teste

EXEC sp_2

PRINT 'DEPOIS DE EXECUTAR sp_2'
SELECT * FROM #Teste
DROP TABLE #Teste
GO

CREATE PROCEDURE sp_2
AS

SET NOCOUNT ON

INSERT INTO #Teste(Campo01, Campo02)
VALUES (1,'Valor 4 - SP_2')
INSERT INTO #Teste(Campo01, Campo02)
VALUES (2,'Valor 5 - SP_2')
INSERT INTO #Teste(Campo01, Campo02)
VALUES (3,'Valor 6 - SP_2')
GO


STORED PROCEDURE UNION ALL

Código:


IF OBJECT_ID('Monitor_Status','P') IS NOT NULL
DROP PROCEDURE Monitor_Status
GO

CREATE PROCEDURE Monitor_Status
AS

SELECT id,id_pc, bk, data
FROM tbl_001 WITH(NOLOCK)
WHERE status_m = 'A'

UNION

SELECT id,id_pc, bk, data
FROM tbl_001f WITH(NOLOCK)
WHERE status_m = 'A'

UNION

SELECT id,id_pc, bk, data
FROM tbl_004 WITH(NOLOCK)
WHERE status_m = 'A'


e no vb basta associar um recordset

Set rs = cn.Execute("Monitor_Status")


USANDO DECLARE PARA SELECT

Código:


CREATE PROCEDURE SP_CLIENTES_SELECT_DECLARE

@PAR1 VARCHAR(10)

AS

DECLARE @SQLString NVARCHAR(500)
DECLARE @TABELA AS NVARCHAR(10)
SET @TABELA = @par1
SET @SQLString = N'SELECT CODIGO , NOME , DATA , VALOR FROM CLIENTES' + @TABELA
EXEC(@SQLString)
GO


SELECIONA CLIENTES

Código:


Create procedure SelecionaCliente
@Codigo int,
@Campo nvarchar(30),
@Conteudo nvarchar(200)
as

Begin

IF @Codigo = 0
Begin
Select clicod, clicliente, clinfantasia, clicontato, cliie, clicnpj,
cliendereco, clibairro, climunicipio, cliuf, clicep, clitel1,
clitel2, clitel3, cliobs From tabCliente where cliCliente like @Conteudo + '%'
end
Else
Begin

DECLARE @Command nvarchar(4000)

set @Command = 'Select clicod, clicliente, clinfantasia, clicontato, cliie, clicnpj,cliendereco, clibairro, climunicipio, cliuf, clicep, clitel1, clitel2, clitel3, cliobs From tabCliente where '+ @Campo + ' = ' + CHAR(39) + CONVERT(nvarchar,@Conteudo) + CHAR(39)

EXEC sp_executasql @Command

end
end

CONSULTAR CNPJ ANTES DE INSERIR CLIENTES

Código:


Create Procedure SP_Inserir (@CNPJ varchar(14), @Codigo int, @Nome varchar(50), @CodigoRetorno int output)

as
Begin
Begin Tran
If @CNPJ = (select CNPJ from tabela where cod = @Codigo int)
begin
end
else
begin
Insert into TabCliente(clinome) value(@Nome)

set @CodigoRetorno = SCOPE_IDENTTY()

return
end
Commit Tran
end


EXECUTAR SP PELO VB

Código:

cnx.Execute "[Nome_da_Stored Procedure] " & Param1 & "," & Param2 & "," & Param3


CURSOR STORE PROCEDURE

Código:

PRIMEIRO VC TEM Q ABRIR UMA TABELA "VIRTUAL"
CREATE TABLE #classificacao (nome varchar(80),ENTRADA bigint,SAIDA BIGINT,EST_ANTERIOR BIGINT)

DEPOIS DECLARAR O CURSOR COMO FOR E CARREGAR O SELECT
DECLARE TABELA CURSOR FOR
select PROD_ID,PROD_NOME_AMIGAVEL from estoque_produtos

DEPOIS ABRIR A TABELA E INSERIR OS DADOS
OPEN TABELA
FETCH NEXT FROM TABELA INTO @ID_PROD, @NOME_PROD
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM TABELA INTO @ID_PROD,@NOME_PROD


CURSOR STORE PROCEDURE

Código:


CREATE PROCEDURE USP_LISTAGEM_RIO
@DATA AS VARCHAR(12)

AS
DECLARE @ID_BANCO AS INT
SET @ID_BANCO = 4 -- RIO DE JANEIRO

DECLARE @ID_DCP AS INT
DECLARE @LIG_N AS INT
DECLARE @TEMPO_N AS INT
DECLARE @LIG_R AS INT
DECLARE @TEMPO_R AS INT
DECLARE @ID_DESC AS INT
DECLARE @DESCRICAO AS VARCHAR(30)


CREATE TABLE #TEMP(
ID_DCP INT,
DATA VARCHAR(12),
DESCRICAO VARCHAR(50),
LIG_NORMAL INT ,
TEMPO_NORMAL INT,
LIG_REDUZIDO INT,
TEMPO_REDUZIDO INT)

--Cliente XPTO - CODIGO - 8
SET @DESCRICAO = 'XPTO'
SET @ID_DESC = 8
SELECT
@ID_DCP = ID_DCP,
@LIG_N = LIG_NORMAL_DCP,
@TEMPO_N = TEMPO_NORMAL_DCP,
@LIG_R = LIG_REDUZIDO_DCP,
@TEMPO_R = TEMPO_REDUZIDO_DCP
FROM TBL_DETALHE_CAIXA_POSTAL
WHERE ID_DESC_DCP = @ID_DESC
AND DATA_DCP LIKE @DATA
AND ID_BD_DCP = @ID_BANCO

INSERT INTO #TEMP(ID_DCP,DATA,DESCRICAO,LIG_NORMAL,TEMPO_NORMAL,LIG_REDUZIDO,TEMPO_REDUZIDO)
VALUES(@ID_DCP,@DATA,@DESCRICAO,@LIG_N,@TEMPO_N,@LIG_R,@TEMPO_R)

-- assim até o cliente N.


--depois disso

SELECT * FROM #TEMP

DROP TABLE #TEMP

/* Fim da PROCEDURE */


VARIAVEL IS

Código:


CREATE PROCEDURE SP_ALIQUOTA(
PID CPONUMVAL0,
PICMS MONE,
PISS MONE,
TIPO FJ)
IS
CODIGO INTEGER;
BEGIN
SELECT NVL(MAX(ID_TABELA, 0) + 1 INTO CODIGO FROM TABELA;
IF (TIPO = 'I') THEN
INSERT INTO TBLALIQUOTA(CODIGO,ICMS,ISS)
VALUES(:PID, :PICMS, :PISS);
IF (TIPO = 'A') THEN
UPDATE TBLALIQUOTA
SET ICMS = :PICM, ISS= :PISS
WHERE CHAVE = :PID;
IF (TIPO = 'E') THEN
DELETE FROM TBLALIQUOTA WHERE CHAVE = :PID;
SUSPEND;
END;


STORED PROCEDURE EM ASP

Código:


Dim CN
Dim CMD
set cn=createobject("adodb.connection")
set cmd=createobject("adodb.command")
cmd.commandtext="nomedaprocedure"
cmd.commandtype=4

cn.open "string de conexão"
set cmd.activeconnection=cn 'Neste momento os parâmetros da procedure são recuperados

cmd.parameters("@valor")="teste"

'A procedure pode ser um select ou um insert. Se for um select, devolve um recordset

set rs=cmd.execute

'Caso contrario não devolve
cmd.execute


EXECUTAR STORE PROCEDURE INFORMANDO PARAMETRO

Código:

EXECUTE TESTE '00001'