SCRIPT: Creación de Procedimientos Almacenados

--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 8 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'