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

Responder a Anónimo Cancelar la respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.