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