SQL BÁSICO 2


__________________________________________________________________________________
CREATE FUNCTION  [dbo].[rec_validar_nie](@NIE VARCHAR(20))
RETURNS BIT
AS
BEGIN
DECLARE @NIE_num varchar(20)
DECLARE @NIE_let_ini varchar(20)
DECLARE @NIE_let_final varchar(20)
DECLARE @letra_NIF varchar(1)
DECLARE @RESULT BIT
DECLARE @NIF_result AS VARCHAR(20)

--comprobar 9 dígitos
IF LEN(@NIE)=9 AND @NIE IS NOT NULL
BEGIN
SET @NIE_let_ini =LEFT(@NIE,1) -- extraer letra inicial
SET @NIE_let_final =RIGHT(@NIE,1) -- extraer letra final
SET @NIE_num=SUBSTRING(@NIE,2,7) -- extraer parte numérica

-- validar letra inicial, final y parte numérica
IF (SELECT ISNUMERIC (@NIE_let_ini))=0
AND (SELECT ISNUMERIC (@NIE_let_final))=0
AND (SELECT ISNUMERIC (@NIE_num))=1
BEGIN
--Se sustituye la letra inicial por x=0,y=1, z=2, y se trata como un NIF
IF (@NIE_let_ini='x') SET @NIE_let_ini=0
IF (@NIE_let_ini='y') SET @NIE_let_ini=1
IF (@NIE_let_ini='z') SET @NIE_let_ini=2
SET @NIF_result=@NIE_let_ini + @NIE_num + @NIE_let_final

--Ejecutamos el procedimiento para validar NIF, OBTIENE TRUE O FALSE
SET @RESULT= (SELECT dbo.rec_validar_nif(@NIF_result))

END
ELSE
BEGIN
SET @RESULT= 0
END

END
ELSE
BEGIN
SET @RESULT= 0
END
RETURN @RESULT
END
GO

__________________________________________________________________________________
CREATE FUNCTION  [dbo].[rec_validar_nif](@NIF VARCHAR(20))
RETURNS BIT
AS
BEGIN
DECLARE @NIF_sin_letra varchar(20)
DECLARE @letra_NIF varchar(1)
DECLARE @RESULT BIT

--Comrpueba que son 9 dígitos
IF @NIF IS NOT NULL AND (SELECT LEN(@NIF))=9
BEGIN
--EXTRAEMOS LA LETRA DEL NIF
SET @letra_NIF=RIGHT(@NIF,1)
--NIF SIN LETRA
set @NIF_sin_letra= SUBSTRING ( @NIF ,1 , 8 ) 

--VALIDAMOS PRIMERO QUE TENGA LA FORMA CORRECTA EL NIF

--Comprueba que hay una parte númerica y la letra
IF (SELECT ISNUMERIC(@NIF_sin_letra)) = 1 AND (SELECT ISNUMERIC(@letra_NIF)) =0

BEGIN
--SE CALCULA LA LETRA CORRECTA
DECLARE @letras varchar(23)='TRWAGMYFPDXBNJZSQVHLCKE'
DECLARE @letra_calculada varchar(1)
SET @letra_calculada= Substring(@Letras, (@NIF_sin_letra % 23) + 1, 1)

--DEVUELVE 1 SI LA LETRA ES  CORRECTA
IF @letra_calculada =@letra_NIF SET @RESULT= 1
ELSE SET @RESULT= 0
END
ELSE
SET @RESULT= 0
END
ELSE
BEGIN
SET @RESULT= 0
END
RETURN @RESULT
END
GO
__________________________________________________________________________________
CREATE     FUNCTION [dbo].[rec_ValidarIBAN]
      ( @vcharIban  AS  VARCHAR(34) )
      RETURNS BIT
AS

  BEGIN

      DECLARE @vcharIbanAux AS VARCHAR (40)
      DECLARE @i AS SMALLINT
      DECLARE @vcharIbanTransformado AS VARCHAR(400)
      DECLARE @LEN AS INT
      DECLARE @modulo AS INT
      DECLARE @bprimera AS INT
      DECLARE @MENOS AS INT
      DECLARE @Caracteres AS VARCHAR (70)

      SET @Caracteres =  'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'

      IF LEN(@vcharIBAN) < 15  --16
      BEGIN
            RETURN 0 
      END

      IF  ISNUMERIC (SUBSTRING(@vcharIban,1,2)) = 1
      BEGIN
            RETURN 0 
      END


      IF PATINDEX('%[^' + @Caracteres + ']%', @vcharIban) > 0
      BEGIN
            RETURN 0
      END

     
      SET @vcharIbanaux = SUBSTRING(@vcharIban, 5, LEN(RTRIM(@vcharIban)) - 4) +  +  SUBSTRING(@vcharIban, 1,4) 

      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'A', '10')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'B', '11')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'C', '12')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'D', '13')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'E', '14')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'F', '15')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'G', '16')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'H', '17')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'I', '18')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'J', '19')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'K', '20')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'L', '21')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'M', '22')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'N', '23')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'O', '24')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'P', '25')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'Q', '26')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'R', '27')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'S', '28')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'T', '29')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'U', '30')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'V', '31')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'W', '32')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'X', '33')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'Y', '34')
      SET @vcharIbanaux =  REPLACE(@vcharIbanaux, 'Z', '35')
     

      SET @vcharIbanTransformado = @vcharIbanAux

      SET @len = 0

BEGIN
      SET @bprimera = 0

      WHILE  LEN(@vcharIbanTransformado) > 0
            BEGIN
                  IF @bprimera = 0
                        BEGIN
                             SET @modulo =  (SELECT  CAST(CAST(SUBSTRING(@vcharIbanTransformado, 1, 9) AS BIGINT ) % 97 as VARCHAR(20) ))
                             SET @LEN = (SELECT LEN(RTRIM(CAST(@modulo AS VARCHAR(20)))))

                             SET @MENOS = LEN((SELECT SUBSTRING(@vcharIbanTransformado, 10, (9  - @LEN )) ))
           

                             SET @vcharIbanAux =  CAST(@modulo  AS VARCHAR(20))  + (SELECT SUBSTRING(@vcharIbanTransformado, 10, (9  - @LEN )) )
                             SET @bprimera = 1
                             SET @vcharIbanTransformado = SUBSTRING(@vcharIbanTransformado, 10, len(@vcharIbanTransformado) -9)

                        END
                  ELSE
                        BEGIN
                             SET @modulo =  (SELECT CAST(@vcharIbanAux AS BIGINT ) % 97 )

                             SET @MENOS = LEN((SELECT SUBSTRING(@vcharIbanTransformado, 1, (9  - @LEN )) ))

                             SET @vcharIbanAux = CAST(@modulo  AS VARCHAR(20)) + (SELECT SUBSTRING(@vcharIbanTransformado, @MENOS + 1, (9  - @LEN )) )
         
                             IF LEN (@vcharIbanTransformado) >= @MENOS
                                   BEGIN
                                         SET @vcharIbanTransformado = SUBSTRING(@vcharIbanTransformado, @MENOS + 1, len(@vcharIbanTransformado) - @MENOS + 1) 

                                   END
                              ELSE
                                   BEGIN
                                         SET @vcharIbanTransformado = SUBSTRING(@vcharIbanTransformado, @MENOS + 1, LEN (@vcharIbanTransformado) - @MENOS + 1)
                                   END

                             IF LEN(@vcharIbanTransformado) - @MENOS < = 0
                                   BEGIN

                                         IF (SELECT CAST(@vcharIbanaux AS BIGINT ) % 97 ) = 1
                                               BEGIN     
                                                     RETURN 1
                                               END 
                                   END
                        END
            END
END


RETURN 0

END
GO
__________________________________________________________________________________
CREATE FUNCTION [dbo].[rec_ValidarBIC] (@strBIC AS VARCHAR(11))

RETURNS BIGINT

AS


BEGIN
      DECLARE @I AS INT
    DECLARE @X AS BIGINT


      SET @X = 1
      SET @I = 1
      WHILE @I < 7
            BEGIN

                  IF NOT ASCII(SUBSTRING(@strBIC,@I,1)) BETWEEN 65 AND 90
                        BEGIN
                             SET @X = 0
                        END
                        SET @I = @I + 1
            END

      IF  NOT ASCII(SUBSTRING(@strBIC,7,1)) BETWEEN 65 AND 90 AND  not ASCII(SUBSTRING(@strBIC,7,1)) BETWEEN 50 AND 57

                        BEGIN
                             SET @X = 0
                        END
     
      IF  NOT ASCII(SUBSTRING(@strBIC,8,1)) BETWEEN 65 AND 78 AND         
            NOT ASCII(SUBSTRING(@strBIC,8,1)) BETWEEN 80 AND 90 AND
            NOT ASCII(SUBSTRING(@strBIC,8,1)) BETWEEN 48 AND 57

                        BEGIN
                             SET @X = 0
                        END
     

      IF  (SELECT COUNT(*) FROM DBO.rec_paises WHERE iso2  = SUBSTRING(@strBIC,5,2)) = 0

            BEGIN
                  SET @X = 0
            END

      IF LEN(@strBIC) <> 8 AND LEN(@strBIC) <> 11
            BEGIN
                  SET @X = 0
            END

      IF LEN(@strBIC) = 11
            BEGIN

                  SET @I = 9
                  WHILE @I < 12
                        BEGIN

                                   IF  not ASCII(SUBSTRING(@strBIC,@I,1)) BETWEEN 65 AND 90 AND NOT ASCII(SUBSTRING(@strBIC,@I,1)) between 48 and 57
                                   BEGIN
                                         SET @X = 0
                                   END
                             SET @I = @I + 1
                        END

            END

RETURN (@X)
END
GO

__________________________________________________________________________________

SQL BÁSICO 1

/**/
--
***CREAR UNA TABLA
use [dnndev.me]
CREATE TABLE rec_dat_user(
UserID INT NOT NULL,
edad VARCHAR(250) NULL,
tip_doc VARCHAR(250) NULL,
n_doc VARCHAR(250) NULL,
sex VARCHAR(250) NULL,
telefono VARCHAR(250) NULL,
idioma VARCHAR(250) NULL,
cp VARCHAR(250) NULL,
direccion VARCHAR(250) NULL,
localidad VARCHAR(250) NULL,
pais VARCHAR(250) NULL,
provincia VARCHAR(250) NULL,
c_domicilio VARCHAR(250) NULL,
c_poblacion VARCHAR(250) NULL,
c_pais VARCHAR(250) NULL,
c_cod_iban VARCHAR(250) NULL,

CONSTRAINT PK_USERID PRIMARY KEY(UserId),
CONSTRAINT FK__DAT_USER  FOREIGN KEY(UserID)  REFERENCES Users(UserId)
)
_______________________
**********COMANDOS BÁSICOS

insert into rec_dat_user (UserId)  values (58)

DELETE from rec_dat_user where UserId= 58

UPDATE rec_dat_user SET telefono ='' WHERE UserId=58

********************
CREAR UN INDICE
use personas
CREATE UNIQUE NONCLUSTERED INDEX U_ID1 ON prueba(fila1)

Nombre del indice  ON table(campo)
CREATE INDEX

*************
MODIFICAR INDICE
alter table Palabras add
  constraint PK_Palabras primary key clustered (IdPagina, Orden)
GO

_____________
****EJEMPLO DE JOIN

--Obtiene gestor especialista asignado a esa reclamación
SELECT FirstName + ' '+ LastName + '|'+ CONVERT(VARCHAR(250), u.UserId) AS Gestor
FROM Users u
INNER JOIN rec_esp_asignado a
ON u.UserId=a.UserId

WHERE  a.ReclamaId=@hd_id_reclamacion  AND a.Asignado=1



***************EJECUTAR FUNCION, (también con select tras introducir el valor en una variable es posible)

EXEC rec_Actualizar_usuario [NewUserId], '[Telefono]', '[FirstName]', '[LastName]'

________________

***************CREAR UNA FUNCION

CREATE FUNCTION [dbo].[rec_sum_hora](@hora1 as time,@hora2 as time)
RETURNS time
AS
BEGIN
DECLARE @dtsuma AS datetime,
@tsuma AS time,
@horas AS numeric,
@minutos AS numeric,
@segundos AS numeric

set @horas = DATEPART(hour,@hora2)
set @minutos = DATEPART(minute,@hora2)
set @segundos = DATEPART(second,@hora2)

set @dtsuma = DATEADD(hour,@horas,@hora1)
set @dtsuma = DATEADD(minute,@minutos,@dtsuma)
set @dtsuma = DATEADD(second,@segundos,@dtsuma)
set @tsuma = CONVERT(time,@dtsuma)

RETURN @tsuma 
END


**************** CREAR PROCEDIMENTO

CREATE PROCEDURE [dbo].[rec_act_gestion_update_rbp](
   @ReclamacId BIGINT
  ,@tip VARCHAR(10)
  ,@indemnizacion VARCHAR(150)
  ,@txtbp_importe VARCHAR(150)
  ,@txtbp_canjea_acciones VARCHAR(150)
  ,@txtbp_test_idonei VARCHAR(150)
  ,@txtbp_estudi VARCHAR(150)
  ,@txtbp_profesion VARCHAR(150)
  ,@txtbp_inv_otros_prod VARCHAR(150)
  ,@txtbp_otro_tipo VARCHAR(150)

)
AS
IF ISNUMERIC(@ReclamacId)!=''
BEGIN
UPDATE rec_dat_client SET
indemnizacion=@indemnizacion
WHERE ReclamacId =@ReclamacId
IF @tip ='rbp'
BEGIN
IF EXISTS(SELECT ReclamacId FROM rec_dat_client_rbp WHERE ReclamacId =@ReclamacId )
UPDATE rec_dat_client_rbp SET
importe=@txtbp_importe
,canjea_acciones=@txtbp_canjea_acciones
,test_idonei=@txtbp_test_idonei
,estudi=@txtbp_estudi
,profesion=@txtbp_profesion
,inv_otros_prod=@txtbp_inv_otros_prod
,otro_tipo=@txtbp_otro_tipo

WHERE ReclamacId =@ReclamacId

END
END


________________OTRO EJEMPLO DE PROCEDIMENTO

ALTER PROCEDURE dbo.rec_AsignarGestor(@UserId int,@ReclamaId bigint,@MENSAJE VARCHAR(100) OUTPUT)
AS
BEGIN
--SI NO ES EL ACTUAL GESTOR
IF NOT EXISTS (SELECT Asignado FROM rec_esp_asignado WHERE (ReclamaId =@ReclamaId AND  UserId=@UserId AND Asignado=1))

BEGIN
DECLARE @Fecha_act AS DATETIME=(SELECT[dbo].[rec_sum_hora](GETDATE(),'01:00'))
--BUSCAMOS EL ACTUAL GESTOR
DECLARE @GestorActual INT=(SELECT UserId FROM rec_esp_asignado WHERE (ReclamaId =@ReclamaId AND Asignado=1))

--LO DESASINAMOS COMO GESTOR Y LE PONEMOS FECHA FIN GESTIÓN
UPDATE rec_esp_asignado SET Asignado=0, fecha_fin=@Fecha_act
WHERE UserId=@GestorActual AND ReclamaId =@ReclamaId AND Asignado=1

--INSERTAMOS NUEVO GESTOR CON SU FECHA DE INCIO GESTIÓN
IF  @UserId IS NOT NULL AND @ReclamaId IS NOT NULL
BEGIN
DECLARE @Actul_asignado BIT
INSERT INTO rec_esp_asignado (UserId,ReclamaId,Fecha_ini,Asignado)
VALUES(@UserId,@ReclamaId,@Fecha_act,1)
END
SET @MENSAJE= 'USUARIO ASIGNADO CORRECTAMENTE'
END
ELSE
BEGIN
SET @MENSAJE='YA ERA EL USUARIO ASIGNADO'
END
END
GO

****************EJEMPLO PROCEDIMENTO CON PARAMETROS SALIDA
ALTER PROCEDURE dbo.rec_AsignarGestor(@UserId int,@ReclamaId bigint,@MENSAJE VARCHAR(100) OUTPUT)
AS
BEGIN
--SI NO ES EL ACTUAL GESTOR
IF NOT EXISTS (SELECT Asignado FROM rec_esp_asignado WHERE (ReclamaId =@ReclamaId AND  UserId=@UserId AND Asignado=1))

BEGIN
DECLARE @Fecha_act AS DATETIME=(SELECT[dbo].[rec_sum_hora](GETDATE(),'01:00'))
--BUSCAMOS EL ACTUAL GESTOR
DECLARE @GestorActual INT=(SELECT UserId FROM rec_esp_asignado WHERE (ReclamaId =@ReclamaId AND Asignado=1))

--LO DESASINAMOS COMO GESTOR Y LE PONEMOS FECHA FIN GESTIÓN
UPDATE rec_esp_asignado SET Asignado=0, fecha_fin=@Fecha_act
WHERE UserId=@GestorActual AND ReclamaId =@ReclamaId AND Asignado=1

--INSERTAMOS NUEVO GESTOR CON SU FECHA DE INCIO GESTIÓN
IF  @UserId IS NOT NULL AND @ReclamaId IS NOT NULL
BEGIN
DECLARE @Actul_asignado BIT
INSERT INTO rec_esp_asignado (UserId,ReclamaId,Fecha_ini,Asignado)
VALUES(@UserId,@ReclamaId,@Fecha_act,1)
END
SET @MENSAJE= 'USUARIO ASIGNADO CORRECTAMENTE'
END
ELSE
BEGIN
SET @MENSAJE='YA ERA EL USUARIO ASIGNADO'
END
END
GO


________________LLAMADA PROCEDIMENTOS

DECLARE @MENSAJE VARCHAR(100)

EXECUTE dbo.[rec_AsignarGestor] '70','687',@MENSAJE OUTPUT
SELECT @MENSAJE


_________________VISTA
CREATE VIEW vw_Names 
   AS 
   SELECT ProductName, Price FROM Products; 
GO 


_________________TRIGGER
- Cremamos un Trigger sobre la tabla expedientes
CREATE TRIGGER StatusChangeDateTrigger
ON expedientes
 AFTER UPDATE AS
 -- ¿Ha cambiado el estado?
 IF UPDATE(state)
 BEGIN
-- Actualizamos el campo stateChangedDate a la fecha/hora actual
UPDATE expedientes SET stateChangedDate=GetDate() WHERE code=(SELECT code FROM inserted);

    -- A modo de auditoría, añadimos un registro en la tabla expStatusHistory
INSERT INTO expStatusHistory  (code, state) (SELECT code, state FROM deleted WHERE code=deleted.code);

    -- La tabla deleted contiene información sobre los valores ANTIGUOS mientras que la tabla inserted contiene los NUEVOS valores.
    -- Ambas tablas son virtuales y tienen la misma estructura que la tabla a la que se asocia el Trigger.
 END;

_________________EJEMPLO UNION

Para unir dos tablas con los mismo campos o similares, coloca la cabecera de la primera SELECT e inserta todos los datos de las dos tablas como si fuera una

Select Col1, Col2, Col3, Col4, Col5 from Table1
Union
Select Col1, Col2, Col3, Null as Col4, Null as Col5 from Table2
_____________________