Just another WordPress.com weblog

Archivo para la Categoría "SQL"

Querys Utiles

Buscar un campo en una todas las tablas:

SELECT TABLE_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%NombreDeCampo%’

Buscar un Stored Procedure por algun fragmento del nombre:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%palabraDelStored%’
AND ROUTINE_TYPE=’PROCEDURE’

Buscar Stored Procedures por texto:

SELECT distinct name SP_Name FROM [sysobjects] INNER JOIN [syscomments] ON [sysobjects].id = [syscomments].id where xtype = ‘P’ and text like ‘%textoabucar%’

SQL Replace()

Replaces all occurrences of a specified string value with another string value.

Syntax
REPLACE ( string_expression , string_pattern , string_replacement )

Example
select REPLACE(‘holca’,’c’,”) –hola

Funciones Fecha SQL

DATEPART (Transact-SQL) :

Returns an integer that represents the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Example:

–19/05/2011
select DATEPART(DD,GETDATE()) — 19
select DATEPART(mm,GETDATE()) — 5
select DATEPART(yy,GETDATE()) — 2011

Another Way

select DAY(GETDATE())–19
select MONTH(getdate())–5
select YEAR(getdate())–2011

SQL Transaction

CREATE PROCEDURE DeleteDepartment
(
   @DepartmentID    int
)
AS
– This sproc performs two DELETEs.  First it deletes all of the
– department’s associated employees.  Next, it deletes the department.
– STEP 1: Start the transaction
BEGIN TRANSACTION
– STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement
DELETE FROM Employees
WHERE DepartmentID = @DepartmentID
– Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    — Rollback the transaction
    ROLLBACK
    — Raise an error and return
    RAISERROR (‘Error in deleting employees in DeleteDepartment.’, 16, 1)
    RETURN
 END
DELETE FROM Departments
WHERE DepartmentID = @DepartmentID
– Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    — Rollback the transaction
    ROLLBACK
    — Raise an error and return
    RAISERROR (‘Error in deleting department in DeleteDepartment.’, 16, 1)
    RETURN
 END
– STEP 4: If we reach this point, the commands completed successfully
–         Commit the transaction….
COMMIT

Procedimientos Almacenados SQL Server y MySQL

SQL Server

CREATE PROCEDURE usp_name
@filtro1 int,
@filtro2 int
as
SELECT DATO1, DATO2
FROM TABLA1
WHERE DATO3= @filtro1 and
dato4=@filtro2

MySQL

DROP PROCEDURE IF EXISTS infact.movimientos;
CREATE PROCEDURE infact.`movimientos`(
vfecha date
,vcodigo varchar(10)
,vtipo_mov varchar(20)
,vdescr_mov varchar(20)
,vdocumento int(11)
,vcantidad int(11)
)
BEGIN
insert into movimientos (
Fecha
,codigo
,tipo_mov
,descr_mov
,documento
,cantidad

) VALUES (
vFecha
,vcodigo
,vtipo_mov
,vdescr_mov
,vdocumento
,vcantidad

);

END;

Ejemplo Crear Tabla MySQL

CREATE TABLE `fac_enc` (
`numero_fac` bigint(15) NOT NULL,
`Cliente` varchar(100) NOT NULL,
`Fecha` date NOT NULL,
`Total` decimal(10,2) NOT NULL,
`dui` varchar(20) NOT NULL,
`nit` varchar(20) DEFAULT NULL,
`coorelativo` int(11) NOT NULL AUTO_INCREMENT,
`nrc` varchar(20) DEFAULT NULL,
`direccion` varchar(500) DEFAULT NULL,
`departamento` varchar(100) DEFAULT NULL,
`giro` varchar(100) DEFAULT NULL,
`codigo_cliente` int(11) DEFAULT NULL,
`condiciones` varchar(20) DEFAULT NULL,
`codvendedor` int(11) DEFAULT NULL,
PRIMARY KEY (`coorelativo`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Crear tabla SQL

Las tablas son la estructura básica donde se almacena la información en la base de datos. Dado que en la mayoría de los casos, no hay forma de que el proveedor de base de datos sepa con antelación cuales son sus necesidades de almacenamiento de datos, es probable que necesite crear tablas en la base de datos usted mismo. Muchas herramientas de base de datos le permiten crear tablas sin ingresar SQL, pero debido a que las tablas son los contenedores de toda la información, es importante incluir la sintaxis CREATE TABLE en esta guía de referencia.

Antes de sumergirnos en la sintaxis SQL para CREATE TABLE, es una buena idea comprender lo que se incluye en una tabla. Las tablas se dividen en filas y columnas. Cada fila representa una parte de los datos, y cada columna puede pensarse como la representación de un componente de aquella parte de los datos. Entonces, por ejemplo, si tenemos una tabla para registrar la información del cliente, las columnas pueden incluir información tal como Primer Nombre, Apellido, Dirección, Ciudad, País, Fecha de Nacimiento y demás. Como resultado, cuando especificamos una tabla, incluimos los títulos de columna y los tipos de datos para esta columna en particular.

Entonces ¿Qué son los tipos de datos? Generalmente, los datos se generan en formas variadas. Podría ser un entero (tal como 1), un número real (tal como 0,55), una línea (tal como ‘sql’), una fecha/expresión de tiempo (tal como ’25-ene-2000 03:22:22′), o incluso en formato binario. Cuando especificamos una tabla, necesitamos especificar el tipo de dato asociado con cada columna (es decir, especificaremos que ‘First_Name’ es de char(50) tipo – lo que significa que es una línea con 50 caracteres). Una cosa a tener en cuenta es que las diferentes bases de datos relacionales permiten diferentes tipos de datos, entonces es prudente consultar primero una referencia específica de base de datos.

La sintaxis SQL para CREATE TABLEes

CREATE TABLE “nombre_tabla”
(“columna 1″ “tipo_de_datos_para_columna_1″,
“columna 2″ “tipo_de_datos_para_columna_2″,
… )

Entonces, si debemos crear una tabla para el cliente tal como se especifica anteriormente, ingresaríamos

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

A veces, deseamos brindar un valor predeterminado a cada columna. Se utiliza un valor predeterminado cuando no especifica un valor de columna cuando ingresa datos a la tabla. Para establecer un valor predeterminado, agregue “[valor] Predeterminado” luego de la instrucción de tipo de datos. En el ejemplo anterior, si deseamos predeterminar una columna “Address” como “Desconocida” y Ciudad como “Mumbai”, ingresaríamos

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default ‘Unknown’,
City char(50) default ‘Mumbai’,
Country char(25),
Birth_Date date)

Estructuras de control en Transact SQL

Estructura condicional IF

La estuctura condicional IF permite evaluar una expresion booleana (resultado SI – NO), y ejecutar las operaciones contenidas en el bloque formado por BEGIN END.

IF ()
BEGIN

END
ELSE IF ()
BEGIN

END
ELSE
BEGIN

END

Ejemplo de la estructura condicional IF.

DECLARE @Web varchar(100),
@diminutivo varchar(3)

SET @diminutivo = ‘DJK’

IF @diminutivo = ‘DJK’
BEGIN
PRINT ‘www.devjoker.com’
END
ELSE
BEGIN
PRINT ‘Otra Web (peor!)’
END

La estructura IF admite el uso de subconsultas:

DECLARE @coPais int,
@descripcion varchar(255)
set @coPais = 5
set @descripcion = ‘España’
IF EXISTS(SELECT * FROM PAISES
WHERE CO_PAIS = @coPais)
BEGIN
UPDATE PAISES
SET DESCRIPCION = @descripcion
WHERE CO_PAIS = @coPais
END

ELSE
BEGIN
INSERT INTO PAISES
(CO_PAIS, DESCRIPCION) VALUES
(@coPais, @descripcion)
END

Estructura condicional CASE

La estructura condicional CASE permite evaluar una expresion y devolver un valor u otro.

La sintaxis general de case es:

CASE
WHEN THEN
WHEN THEN
ELSE — Valor por defecto
END
Ejemplo de CASE.

DECLARE @Web varchar(100),
@diminutivo varchar(3)
SET @diminutivo = ‘DJK’
SET @Web = (CASE @diminutivo
WHEN ‘DJK’ THEN ‘www.devjoker.com’
WHEN ‘ALM’ THEN ‘www.aleamedia.com’
ELSE ‘www.devjoker.com’
END)
PRINT @Web

Otra sintaxis de CASE nos permite evaluar diferentes expresiones:

CASE
WHEN = THEN
WHEN = THEN
ELSE — Valor por defecto

END

El mismo ejemplo aplicando esta sintaxis:

DECLARE @Web varchar(100),
@diminutivo varchar(3)
SET @diminutivo = ‘DJK’

SET @Web = (CASE
WHEN @diminutivo = ‘DJK’ THEN ‘www.devjoker.com’
WHEN @diminutivo = ‘ALM’ THEN ‘www.aleamedia.com’
ELSE ‘www.devjoker.com’
END)
PRINT @Web

Otro aspecto muy interesante de CASE es que permite el uso de subconsultas.

DECLARE @Web varchar(100),
@diminutivo varchar(3)
SET @diminutivo = ‘DJK’

SET @Web = (CASE
WHEN @diminutivo = ‘DJK’ THEN (SELECT web
FROM WEBS
WHERE id=1)
WHEN @diminutivo = ‘ALM’ THEN (SELECT web
FROM WEBS
WHERE id=2)
ELSE ‘www.devjoker.com’
END)
PRINT @Web

Bucle WHILE

El bucle WHILE se repite mientras expresion se evalue como verdadero.

Es el único tipo de bucle del que dispone Transact SQL.

WHILE
BEGIN

END
Un ejemplo del bucle WHILE.

DECLARE @contador int
SET @contador = 0
WHILE (@contador < 100)
BEGIN
SET @contador = @contador + 1

PRINT 'Iteracion del bucle ' + cast(@contador AS varchar)
END

Podemos pasar a la siguiente iteración del bucle utilizando CONTINUE.

DECLARE @contador int
SET @contador = 0
WHILE (@contador < 100)
BEGIN
SET @contador = @contador + 1
IF (@contador % 2 = 0)
CONTINUE
PRINT 'Iteracion del bucle ' + cast(@contador AS varchar)
END

El bucle se dejará de repetir con la instrucción BREAK.

DECLARE @contador int
SET @contador = 0
WHILE (1 = 1)
BEGIN
SET @contador = @contador + 1
IF (@contador % 50 = 0)
BREAK
PRINT ‘Iteracion del bucle ‘ + cast(@contador AS varchar)
END

También podemos utilizar el bucle WHILE conuntamente con subconsultas.

DECLARE @coRecibo int
WHILE EXISTS (SELECT *
FROM RECIBOS
WHERE PENDIENTE = ‘S’)– Ojo, la subconsulta se ejecuta
— una vez por cada iteracion
— del bucle!
BEGIN
SET @coRecibo = (SELECT TOP 1 CO_RECIBO
FROM RECIBOS WHERE PENDIENTE = ‘S’)
UPDATE RECIBOS
SET PENDIENTE = ‘N’
WHERE CO_RECIBO = @coRecibo
END

Estructura GOTO

La sentencia goto nos permite desviar el flujo de ejecución hacia una etiqueta. Fué muy utilizada en versiones anteriores de SQL Server conjuntamente con la variable de sistema @@ERROR para el control de errores.

Actualmente, se desaconseja el uso GOTO, recomendandose el uso de TRY – CATCH para la gestion de errores.

DECLARE @divisor int,
@dividendo int,
@resultado int
SET @dividendo = 100
SET @divisor = 0
SET @resultado = @dividendo/@divisor

IF @@ERROR > 0
GOTO error

PRINT ‘No hay error’
RETURN
error:
PRINT ‘Se ha producido una division por cero’

CURSOR SQL

/* Este cursor deja las contraseñas iguales al nombre de usuario.

La tabla Cliente tiene estos tres campos: CliCod, CliUser, CliPass */
– declaramos las variables
declare @cod as int
declare @user as varchar(50)
declare @pass as varchar(50)
– declaramos un cursor llamado “CURSORITO”. El select debe contener sólo los campos a utilizar.
declare CURSORITO cursor for
select CliCod, CliUser, CliPass from Cliente
open CURSORITO
– Avanzamos un registro y cargamos en las variables los valores encontrados en el primer registro
fetch next from CURSORITO
into @cod, @user, @pass
while @@fetch_status = 0
begin
update Cliente set CliPass= @user where CliCod=@cod
– Avanzamos otro registro
fetch next from CURSORITO
into @cod, @rut, @nombres
end
– cerramos el cursor
close CURSORITO
deallocate CURSORITO

Galería

Ejecutar Stored Procedure

EJEMPLO 1

SqlCommand cmd;
SqlDataReader dr;
SqlConnection strCn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CRM_ESAConnectionString"].ConnectionString);
strCn.Open();
cmd = new SqlCommand("Sga_Sp_activacion_Duiencontrados", strCn);
cmd.CommandType =  CommandType.StoredProcedure;
cmd.Parameters.Add("@dui", SqlDbType.VarChar).Value = dui;
cmd.CommandTimeout = 60000;
dr = cmd.ExecuteReader();
account Account = new account();

EJEMPLO2
Dim customSetting As String = System.Configuration.ConfigurationManager.ConnectionStrings("Inventario_y_Facturacion.My.MySettings.infactConnectionString").ConnectionString
Dim cmd As New MySqlClient.MySqlCommand
Dim strCn As MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection(server)
strCn.Open()
cmd = New MySqlClient.MySqlCommand("REPETIDOS", strCn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add((New MySqlClient.MySqlParameter("numero", (Convert.ToInt32(Me.Factura.Text)))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("bandera", bandera)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("fechat", Me.DateTimePicker1.Value)))
cmd.CommandTimeout = 0

Dim values As Int32 = Convert.ToInt32(cmd.ExecuteScalar())
Return values

EJEMPLO 3
Dim a As New Class1()
server = a.conecct()
Dim cmd As New MySqlClient.MySqlCommand
Dim strCn As MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection(server)
strCn.Open()
cmd = New MySqlClient.MySqlCommand("compras_enc", strCn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add((New MySqlClient.MySqlParameter("numero_doc", (Convert.ToInt32(Me.Factura.Text)))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("cliente", Me.NombreTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("fecha", Me.DateTimePicker1.Value)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("total", CONVERTIR(Me.total.Text))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("dui", Me.DuiTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("nit", Me.NitTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("ncr", Me.RegfiscalTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("direccion", Me.DireccionTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("dep", Me.DepartamentoTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("giro", Me.GiroTextBox.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("iva", CONVERTIR(Me.iva.Text))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("retencion", CONVERTIR(Me.percibido.Text))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("sumas", CONVERTIR(Me.sumas.Text))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("codcli", (Convert.ToInt64(Me.CodigoTextBox.Text)))))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("cond", Me.condiciones.Text)))
cmd.Parameters.Add((New MySqlClient.MySqlParameter("codvendedor1", (Convert.ToInt64(Me.ComboBox1.SelectedValue.ToString)))))
cmd.CommandTimeout = 0
Dim mysql As Int16 = cmd.ExecuteNonQuery

Nube de etiquetas

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.