Procedimientos Almacenados

SQL PROGRAMACIÓN CON TRANSACT: Creación de Procedimientos Almacenados
El procedimiento almacenado es el objeto estrella y mas optimizado del SQL Server, permite encapsular un conjunto de instrucciones Transact para realizar una tarea, y que pueden ser ejecutado en respuesta  a una petición del usuario, en esta lección se aprenden los aspectos basicos de un procedimiento.
–1. Crea un procedimiento en la base de datos Activa
 USE NORTHWIND
GO
CREATE PROC DBO.ORDENESNOENTREGADAS
AS
SELECT * FROM ORDERS WHERE ORDERDATE
BETWEEN ‘1996-01-01’ AND ‘1996-31-12’
–2.Información de Procedimientos
EXEC SP_HELP ORDENESNOENTREGADAS
EXEC SP_DEPENDS ORDENESNOENTREGADAS
EXEC SP_HELPTEXT ORDENESNOENTREGADAS
EXEC SP_STORED_PROCEDURES
SELECT * FROM SYSOBJECTS
SELECT * FROM SYSCOMMENTS
SELECT * FROM SYSDEPENDS
–3.Ejecucion de un procedimiento
EXEC ORDENESNOENTREGADAS
sp_executesql ORDENESNOENTREGADAS –USAR SP_EXECUTESQL ES MAS OPTIMO PORQUE GENERA PLANES DE EJECUCION DINAMICAMENTE
EXEC ORDENESNOENTREGADAS WITH RECOMPILE –EXIGE QUE SE RECOMPILE UN NUEVO PLAN
–4.Agregar datos con Insert Into partiendo del resultado del procedimiento
CREATE TABLE [PEDIDOS96]
(
      [OrderID] [int]  NOT NULL ,
      [CustomerID] [nchar] (5)  ,
      [EmployeeID] [int] NULL ,
      [OrderDate] [datetime] NULL ,
      [RequiredDate] [datetime] NULL ,
      [ShippedDate] [datetime] NULL ,
      [ShipVia] [int] NULL ,
      [Freight] [money] NULL  DEFAULT (0),
      [ShipName] [nvarchar] (40)  NULL ,
      [ShipAddress] [nvarchar] (60)  NULL ,
      [ShipCity] [nvarchar] (15) NULL ,
      [ShipRegion] [nvarchar] (15)  NULL ,
      [ShipPostalCode] [nvarchar] (10)  NULL ,
      [ShipCountry] [nvarchar] (15)  NULL ,
)
ON [PRIMARY]
GO
INSERT INTO PEDIDOS96
EXEC ORDENESNOENTREGADAS
Select into
–5. Modificar el procedimiento almacenado
ALTER PROC DBO.ORDENESNOENTREGADAS
AS
SELECT * FROM ORDERS WHERE ORDERDATE
BETWEEN ‘1998-01-01’ AND ‘1998-31-12’
–6.Para eliminar el procedimiento almacenado en la base de datos activa
DROP PROC DBO.ORDENESNOENTREGADAS
–7. Parametros de Entrada
CREATE Proc Insercion
@Tabla1 bit,@compañia varchar(50),@contacto varchar(50)
,@titulo varchar(50),@direccion varchar(50)
as
DECLARE @codigo varchar(50)
SET @codigo=substring(convert(varchar(10),rand()),3,5)
if @Tabla1=1
begin
insert suppliers (companyname,contactname,contacttitle,address)
values
(@compañia,@contacto,@titulo,@direccion)
end
else
begin
insert customers (customerid,companyname,contactname,contacttitle,address)
values
(@codigo,@compañia,@contacto,@titulo,@direccion)
end
–8. Ejecucion con Parametros de entrada
exec insercion
      @compañia=‘cardenas inc’,@contacto=‘Luis’,@titulo=‘Ing’,@direccion=’18calle 1-6 Z.1′
exec Insercion 0,‘cardenas inc9’,‘Victor Cardenas’,‘Ing.’,’30av 23-56 Z.5′
–9.Recompilar un procedimiento Y ESTABLECER ERRORES
ALTER PROC TESTRECOMPILACION
@VALOR  INT
WITH RECOMPILE
AS
DECLARE @STRING VARCHAR(200)
SET @STRING=‘SELECT TOP ‘+ CONVERT(CHAR(1),@VALOR) + ‘ * FROM ORDERS ORDER BY ORDERID’
EXECUTE (@STRING)
IF @@ERROR<>0
BEGIN
PRINT ‘EQUIVOCACION AL INGRESAR MAS DE UN PARAMETRO EN PARAMETRO DE ENTRADA’
END
–10.RECOMPILAR EL PROCEDIMEINTO
EXEC TESTRECOMPILACION WITH RECOMPILE
–11.PROCEDIMIENTO SP_RECOMPILE
SP_RECOMPILE TESTRECOMPILACION
DBCC FREEPROCCACHE –Borra de la cache todos los planes de ejecucion
–12. Parametros de Salida
create Proc Salida
@pais varchar(50),
@valor1 int OUTPUT
as
Select * from customers where country=@pais
set @valor1=@@rowcount
DECLARE @TEST1 INT
exec salida ‘Argentina’,@TEST1
SELECT @TEST1
–13. Procedimientos extendidos
Use Master
go
EXEC master.dbo.xp_cmdshell ‘dir c:\ ‘
EXEC master.dbo.sp_helptext xp_cmdshell –buscar el dll que realiza la funcion
–14. Escribir un mensaje de error
EXEC sp_addmessage
@msgnum = 50010,
@severity = 10,
@lang= ‘us_english’,
@msgtext = ‘Solo se permite un digito’,
@with_log = ‘true’
exec sp_dropmessage 50010,‘us_english’
use master
select * from sysmessages
sp_helptext sp_dropmessage
–15. Usar @@Error
ALTER PROC TESTRECOMPILACION
@VALOR  INT
WITH RECOMPILE
AS
DECLARE @STRING VARCHAR(200)
SET @STRING=‘SELECT TOP ‘+ CONVERT(CHAR(1),@VALOR) + ‘ * FROM ORDERS ORDER BY ORDERID’
EXECUTE (@STRING)
IF @@ERROR<>0
BEGIN
PRINT ‘EQUIVOCACION AL INGRESAR MAS DE UN PARAMETRO EN PARAMETRO DE ENTRADA’
END
–16. usar el error con raiseeror
ALTER PROC TESTRECOMPILACION
@VALOR  INT
WITH RECOMPILE
AS
DECLARE @STRING VARCHAR(200)
SET @STRING=‘SELECT TOP ‘+ CONVERT(CHAR(1),@VALOR) + ‘ * FROM ORDERS ORDER BY ORDERID’
EXECUTE (@STRING)
IF @@ERROR<>0
BEGIN
RAISERROR(50010, 16, 1)
END
exec testrecompilacion 88
–17. borrar de caché todos lo planes de ejecucion
DBCC FREEPROCCACHE
—uso del Return
use pubs
go
CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @param) = ‘CA’
   RETURN 1
ELSE
   RETURN 2
–revision del resultado
declare @rev int
exec @rev=checkstate ‘238-95-7766’
if @rev=1
begin
print ‘bien’
end
else
begin
print ‘mal’
end

One thought on “Procedimientos Almacenados

  • 11 October, 2020 at 3:48 am
    Permalink

    Favor pasarme el script que genera los procedimientos almacenados para el CRUD

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *