15 de abril de 2013

Apuntes de Transact SQL


Para los siguientes ejemplos de Transact SQL la tabla a utilizar se llama empleado y la estructura es esta:



PROCEDIMIENTOS ALMACENADOS T-SQL

Mostrare el código T-SQL de procedimientos almacenados que realizan las acciones de agregar datos, modificar datos, eliminar datos y seleccionar datos sobre los registros de la tabla empleado, al invocar al procedimiento almacenado se retornara un mensaje indicando la acción que realizo el procedimiento, a la vez muestro el código en VB para ejecutar los procedimientos en el cual se mostrara en un label el mensaje que los procedimiento almacenados retornen.

*****  Agregar registros a la tabla empleados(Procedimiento AgregarDatos) *****

USE [registro]
GO
if OBJECT_ID('AgregarDatos','p')is not null
drop proc AgregarDatos
go

CREATE PROCEDURE AgregarDatos
@nombres varchar(50),
@cargo varchar(50)='No Value',
@departamento varchar(50)='No Value',
@jefe varchar(50)='No Value',
@mensaje varchar(100) output
AS
SET NOCOUNT ON

IF (@nombres='')
BEGIN
SELECT @mensaje='Ingresar como minimo el nombre del empleado'
RETURN
END

BEGIN TRY
BEGIN TRAN
INSERT INTO empleado VALUES(@nombres, @cargo, @departamento, @jefe)
COMMIT
SELECT @mensaje='Registro ingresado correctamente en Base de Datos !!!'
END TRY

BEGIN CATCH
ROLLBACK
SELECT @mensaje='Registro no ingresado en Base de Datos !!!'
END CATCH

·         Ejecucion del procedimiento almacenado en codigo VB

Using cnn As New SqlConnection(conexion)
 Try
  cnn.Open()
   Using cmd As New SqlCommand("AgregarDatos", cnn)
         cmd.CommandType = CommandType.StoredProcedure
         cmd.Parameters.AddWithValue("@nombres", "Jose Armando Flores")
         cmd.Parameters.AddWithValue("@cargo", "Gerente de Mantenimiento")
         cmd.Parameters.AddWithValue("@departamento", "Bodega")
         cmd.Parameters.AddWithValue("@jefe", "Gerente General")
         cmd.Parameters.AddWithValue("@estado", "Plaza Permanente")
                     cmd.Parameters.Add("@mensaje", SqlDbType.VarChar, 100)
         cmd.Parameters("@mensaje").Direction = ParameterDirection.Output
         cmd.ExecuteNonQuery()
         Label1.Text = CStr(cmd.Parameters("@mensaje").Value)
    End Using
   Catch ex As Exception
          MsgBox(ex.Message)
  End Try
   End Using


*****  Modificar registros a la tabla empleados(Procedimiento ModificarDatos) *****

USE [registro]
GO
IF OBJECT_ID('ModificarDatos','p')is not null
DROP PROC ModificarDatos
GO

CREATE PROC ModificarDatos
@codigo int,
@nombres varchar(50),
@cargo varchar(50),
@departamento varchar(50),
@jefe varchar(50),
@mensaje varchar(100) output
AS
SET NOCOUNT ON

IF (@codigo='')
BEGIN
SELECT @mensaje='Favor ingresar el codigo del empleado a modificar'
RETURN
END

IF exists (select * from empleado where Codigo=@codigo)
BEGIN TRY
BEGIN TRAN
UPDATE empleado SET Nombres=@nombres, Cargo=@cargo, Departamento=@departamento, Jefe=@jefe WHERE Codigo=@codigo
COMMIT
SET @mensaje='El registro fue actualizado correctamente  !!!'
END TRY

BEGIN CATCH
ROLLBACK
SET @mensaje='El registro no fue actualizado !!!'
END CATCH
ELSE
BEGIN
SET @mensaje='El registro no existe en la base de datos !!!'
END

·         Ejecucion del procedimiento almacenado en codigo VB

Using cnn As New SqlConnection(conexion)
      Try
        cnn.Open()
      Using cmd As New SqlCommand("ModificarDatos", cnn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@codigo", 48)
            cmd.Parameters.AddWithValue("@nombres", "Jose Armando Flores")
            cmd.Parameters.AddWithValue("@cargo", "Gerente de Mantenimiento")
            cmd.Parameters.AddWithValue("@departamento", "Bodega")
            cmd.Parameters.AddWithValue("@jefe", "Gerente General")
            cmd.Parameters.AddWithValue("@estado", "Plaza Permanente")
            cmd.Parameters.Add("@mensaje", SqlDbType.VarChar, 100)
            cmd.Parameters("@mensaje").Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            Label1.Text = CStr(cmd.Parameters("@mensaje").Value)
     End Using
           Catch ex As Exception
            MsgBox(ex.Message)
       End Try
     End Using


*****  Eliminar registros a la tabla empleados(Procedimiento EliminarDatos) *****

USE registro
GO
IF OBJECT_ID('EliminarDatos','p')is not null
DROP PROC EliminarDatos
GO

CREATE PROCEDURE EliminarDatos
@codigo int,
@mensaje varchar(100) output
AS
SET NOCOUNT ON

IF (@codigo='')
BEGIN
SELECT @mensaje='Favor ingresar el codigo del empleado a eliminar'
RETURN
END

IF EXISTS (select * from empleado where Codigo=@codigo)
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM empleado WHERE Codigo=@codigo
COMMIT
SELECT @mensaje='Registro eliminado correctamente de Base de Datos !!!'
END TRY

BEGIN CATCH
ROLLBACK
set @mensaje='Registro no fue eliminado de la Base de Datos !!!'
END CATCH
ELSE
BEGIN
SET @mensaje='Registro no se puede eliminar pues no existe !!!'
END

·         Ejecucion del procedimiento almacenado en codigo VB

Using cnn As New SqlConnection(conexion)
   Try
      cnn.Open()
    Using cmd As New SqlCommand("EliminarDatos", cnn)
         cmd.CommandType = CommandType.StoredProcedure
         cmd.Parameters.AddWithValue("@codigo", 49)
         cmd.Parameters.Add("@mensaje", SqlDbType.VarChar, 100)
         cmd.Parameters("@mensaje").Direction = ParameterDirection.Output
         cmd.ExecuteNonQuery()
         Label1.Text = CStr(cmd.Parameters("@mensaje").Value)
    End Using
   Catch ex As Exception
         MsgBox(ex.Message)
   End Try
    End Using


*****  Mostrar registros de la tabla empleados(Procedimiento MostrarDatos) *****

USE [registro]
GO
IF OBJECT_ID('MostrarDatos','p')is not null
DROP PROCEDURE MostrarDatos
GO

CREATE PROCEDURE MostrarDatos
@estado varchar(50)
AS
SET NOCOUNT ON
BEGIN
select * from empleado where Estado=@estado
END

·         Ejecucion del procedimiento almacenado en codigo VB

Using sql As New SqlConnection(conexion)
   Try
    sql.Open()
     Using cmd As New SqlCommand("MostrarDatos", sql)
           cmd.CommandType = CommandType.StoredProcedure
           cmd.Parameters.AddWithValue("@estado", "plaza permanente")
               Dim da As New SqlDataAdapter(cmd)
               Dim dt As New DataTable
                da.Fill(dt)
                DataGridView1.DataSource = dt
                End Using
    Catch ex As Exception
                MsgBox(ex.Message)
    End Try
 End Using


Funciones T-SQL

Mostrare el código T-SQL de funciones  que  devuelven valores ya sea escalares o bien de tabla, a la vez muestro el código en VB para ejecutar las , en el código VB se mostraran los resultados en un DataGridView la información de tablas y en un label la información de la función escalar

*****  Mostrar registros de la tabla empleados(Funcion MostrarRegistros) *****

USE registro
GO
IF OBJECT_ID('MostrarRegistros')is not null
DROP FUNCTION MostrarRegistros
GO

CREATE FUNCTION MostrarRegistros(
@estado varchar(50)
)
RETURNS TABLE
AS
RETURN
(select * from empleado where Estado=@estado)


·         Ejecucion de la funcion desde SQL

USE registro
GO
SELECT * FROM dbo.MostrarRegistros('Plaza Permanente')
GO


·         Ejecucion de la funcion en codigo VB

Dim sql As New SqlConnection(conexion)
sql.Open()
Dim cmd As New SqlCommand("select * from MostrarRegistros(@parametro)", sql)
cmd.Parameters.AddWithValue("@parametro", "Plaza Permanente")
 Dim da As New SqlDataAdapter(cmd)
 Dim dt As New DataTable
   da.Fill(dt)
   DataGridView1.DataSource = dt


*****  Funcion Escalar(Funcion BuscarNombre) *****

USE registro
GO
IF OBJECT_ID('BuscarNombre')is not null
DROP FUNCTION BuscarNombre
GO

CREATE FUNCTION BuscarNombre(
@codigo int
)RETURNS varchar(50)
AS
BEGIN
DECLARE @nombres varchar(50)
SELECT @nombres=nombres FROM empleado WHERE codigo=@codigo
RETURN @nombres
END


·         Ejecucion de la funcion desde SQL

USE registro
GO
DECLARE @nombres varchar(50)
SET @nombres=dbo.BuscarNombre(1)
PRINT @nombres

·         Ejecucion de la funcion en codigo VB

Using cnn As New SqlConnection(conexion)
 Try
      cnn.Open()
   Using cmd As New SqlCommand("SELECT dbo.BuscarNombre(@codigo)", cnn)
         cmd.Parameters.AddWithValue("@codigo", 1)
         Label1.Text = CStr(cmd.ExecuteScalar())
   End Using
 Catch ex As Exception
         MsgBox(ex.Message)
 End Try
  End Using


*****  Funcion en Linea(Funcion Mostrar) *****

Esta funcion creara una tabla y devolvera el resultado que concuerde con el parametro enviado a la funcion, en el codigo VB se mostraran los resultados en un DataGridView.

USE registro
GO
IF OBJECT_ID('Mostrar')is not null
DROP FUNCTION Mostrar
GO

CREATE FUNCTION Mostrar(
@estado varchar(50)
)
RETURNS @tablita TABLE(
codigo int,
nombres varchar(50),
cargo varchar(50),
dpto varchar(50),
jefe varchar(50)
)
AS
BEGIN
INSERT INTO @tablita
SELECT codigo, nombres, cargo, departamento, jefe
FROM empleado
WHERE estado=@estado
RETURN
END

·         Ejecucion de la funcion desde SQL

USE registro
GO
SELECT * FROM dbo.Mostrar('Plaza Permanente')
GO

·         Ejecucion de la funcion en codigo VB

Dim sql As New SqlConnection(conexion)
sql.Open()
Dim cmd As New SqlCommand("select * from Mostrar(@parametro)", sql)
cmd.Parameters.AddWithValue("@parametro", "Plaza Permanente")
 Dim da As New SqlDataAdapter(cmd)
 Dim dt As New DataTable
   da.Fill(dt)
   DataGridView1.DataSource = dt


TRIGGERS y Vistas T-SQL

Se creara un trigger que guarde en un tabla llamada ActualizacionesEmpleado los datos del empleado que se ha actualizado, asi como la fecha en que se actualizo y el usuario que actualizo los datos, luego mediante una vista se mostraran los datos en un DataGridView utilizando VB.NET

·         Codigo T-SQL del trigger DatosActualizados sobre la tabla empleado

USE [registro]
GO
IF OBJECT_ID('DatosActualizados')is not null
DROP TRIGGER DatosActualizados
GO

CREATE TRIGGER DatosActualizados
on empleado
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
INSERT INTO ActualizacionesEmpleado
select *,getdate(),SYSTEM_USER from INSERTED
END

·         Codigo T-SQL de la vista VistaActualizaciones

USE registro
GO
IF OBJECT_ID('VistaActualizaciones')is not null
DROP VIEW VistaActualizaciones
GO

CREATE VIEW VistaActualizaciones
AS
SELECT * FROM ActualizacionesEmpleado
GO

·         Ejecución de la Vista en código VB

Using sql As New SqlConnection(conexion)
   Try
      sql.Open()
        Dim da As New SqlDataAdapter("select * from VistaActualizaciones", sql)
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt

   Catch ex As Exception
        MsgBox(ex.Message)
   End Try
End Using