Auditoria Trigger – Genera trigger de auditoria de datos update insert delete de una tabla

Set NoCount On

Declare @Tabla As Varchar(100) = ‘Descuentos’
Declare @Operacion As Varchar(100) = ‘ FOR UPDATE ‘
Declare @Cabecera As Varchar(Max) = ”
Declare @Detalle As Varchar(Max) = ”
Declare @printDetalle As Varchar(Max) = ”

Set @Cabecera = ‘
— =============================================
— Author: ‘ + SUSER_SNAME() + ‘
— Create date: ‘ + Cast(year(GetDate()) As Varchar) + RIGHT(‘0’ + Cast(month(GetDate()) As Varchar), 2) + RIGHT(‘0’ + Cast(day(GetDate()) As Varchar), 2) + ‘
— Description: UPDATE TRIGGER for Table: [dbo].[‘ + @Tabla + ‘]
— History:
— Date Author Description
— ———————————————
—
— =============================================
CREATE TRIGGER [dbo].[tr_u_AUDIT_’ + @Tabla + ‘]
ON [dbo].[‘ + @Tabla + ‘]
‘ + @Operacion + ‘
NOT FOR REPLICATION
As
BEGIN
DECLARE
@IDENTITY_SAVE varchar(50),
@AUDIT_LOG_TRANSACTION_ID Int,
@PRIM_KEY nvarchar(4000),
@Inserted bit,
–@TABLE_NAME nvarchar(4000),
@ROWS_COUNT int

SET NOCOUNT ON

Select @ROWS_COUNT=count(1) from inserted
SET @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
HOST_NAME,
APP_NAME,
MODIFIED_BY,
MODIFIED_DATE,
AFFECTED_ROWS,
[DATABASE]
)
values(
”’ + @Tabla + ”’,
”dbo”,
1, — ACTION ID For UPDATE
CASE
WHEN LEN(HOST_NAME()) < 1 THEN ” ”
ELSE HOST_NAME()
END,
CASE
WHEN LEN(APP_NAME()) < 1 THEN ” ”
ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
@ROWS_COUNT,
”’ + DB_NAME() + ”’
)

Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()

SET @Inserted = 0′

print @Cabecera

DECLARE @Columnas TABLE (
ID INT IDENTITY(1, 1)
,ColumnName Varchar(100) Default Null
,PRIMARY KEY(
ID
)
)

Declare @Exclusion As Varchar(200) = ‘Usuario_Alta,Fecha_Alta,Usuario_Cambio,Fecha_Cambio,rowguid,msrepl_tran_version,UsuarioAlta,FechaAlta,UsuarioCambio,FechaCambio’
Insert Into @Columnas (ColumnName)
SELECT
Col.Column_Name
FROM
INFORMATION_SCHEMA.COLUMNS Col
WHERE
Col.TABLE_NAME = @Tabla
And CharIndex(Col.Column_Name, @Exclusion) = 0
AND Col.DATA_TYPE <> ‘text’ — Excluir los tipos TEXT porque no se pueden manipular

Declare @RecordCount As Int = @@RowCount
Declare @Index As Int = 1
Declare @laColumna As Varchar(100)

–Select ‘TSP’,* From @Columnas

DECLARE @LlavePrimaria TABLE (
ID INT IDENTITY(1, 1)
,ColumnName Varchar(100) Default Null
,PRIMARY KEY(
ID
)
)

Insert Into @LlavePrimaria (ColumnName)
Select D.Data From dbo.Split(dbo.fsdba_PrimaryKeyTable(@Tabla),’,’) as D

Declare @RecordCountPK As Int = @@RowCount
Declare @IndexPK As Int = 1
Declare @laColumnaPK As Varchar(100) = ”
Declare @SelectPK As Varchar(1500) = ”
Declare @FromPK As Varchar(1500) = ”
Declare @BandPK As Bit = 0
Declare @Key1 As Varchar(300) = ”
Declare @Key2 As Varchar(300) = ”
Declare @Key3 As Varchar(300) = ”
Declare @Key4 As Varchar(300) = ”

–select ‘teo’, * From @LlavePrimaria PK

— Llave Primaria
While (@IndexPK <= @RecordCountPK) Begin
Select
@LaColumnaPK = ColumnName
From
@LlavePrimaria PK
Where
PK.ID = @IndexPK

If @BandPK = 1 Begin
Set @SelectPK = @SelectPK + ‘ + ” And ” + ‘
Set @FromPK = @FromPK + ‘ And ‘
End

Set @SelectPK = @SelectPK + ‘IsNull(”[‘ + @LaColumnaPK + ‘]=”+ CONVERT(nvarchar(4000), IsNull(OLD.[‘ + @LaColumnaPK + ‘], NEW.[‘ + @LaColumnaPK + ‘]), 0), ”[‘ + @LaColumnaPK + ‘] Is Null”)’
Set @FromPK = @FromPK + ‘(CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0) = CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0) or (NEW.[‘ + @LaColumnaPK + ‘] Is Null and OLD.[‘ + @LaColumnaPK + ‘] Is Null))’

— Llena los campos llave independientes
If @IndexPk = 1
SET @Key1 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’

If @IndexPk = 2
SET @Key2 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’

If @IndexPk = 3
SET @Key3 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’

If @IndexPk = 4
SET @Key4 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’

Set @BandPK = 1
Set @IndexPK += 1
End

If @BandPK = 1 Begin
Set @SelectPK = ‘convert(nvarchar(1500), ‘ + @SelectPK + ‘)’

End

–Select @SelectPk, @FromPK

–Return

— Cada campo
WHILE (@Index <= @RecordCount) BEGIN

— select dbo.fsdba_PrimaryKeyTable(‘hoteles_textos’)
Set @laColumna = (Select ColumnName From @Columnas C Where C.Id = @Index)

Set @printDetalle = ‘
— No. Columna: ”’ + cast(@Index as varchar) + ‘
IF UPDATE ([‘ + @laColumna + ‘]) BEGIN
INSERT INTO dbo.AUDIT_LOG_DATA (
AUDIT_LOG_TRANSACTION_ID
,PRIMARY_KEY_DATA
,COL_NAME
,OLD_VALUE_LONG
,NEW_VALUE_LONG
,KEY1
,KEY2
,KEY3
,KEY4
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
‘ + @SelectPK + ‘
,”’ + @laColumna + ”’
,CONVERT(NVARCHAR(4000), OLD.[‘ + @laColumna + ‘], 0)
,CONVERT(NVARCHAR(4000), NEW.[‘ + @laColumna + ‘], 0)
,’ + Case When IsNull(@Key1, ”) = ” Then ‘NULL’ Else @Key1 End + ‘
,’ + Case When IsNull(@Key2, ”) = ” Then ‘NULL’ Else @Key2 End + ‘
,’ + Case When IsNull(@Key3, ”) = ” Then ‘NULL’ Else @Key3 End + ‘
,’ + Case When IsNull(@Key4, ”) = ” Then ‘NULL’ Else @Key4 End + ‘
FROM deleted OLD
INNER JOIN inserted NEW ON ‘ + @FromPK + ‘
WHERE (
(NEW.[‘ + @laColumna + ‘] <> OLD.[‘ + @laColumna + ‘])
OR (
NEW.[‘ + @laColumna + ‘] IS NULL
AND OLD.[‘ + @laColumna + ‘] IS NOT NULL
)
OR (
NEW.[‘ + @laColumna + ‘] IS NOT NULL
AND OLD.[‘ + @laColumna + ‘] IS NULL
)
)

SET @Inserted = CASE
WHEN @@ROWCOUNT > 0
THEN 1
ELSE @Inserted
END
END
‘ + char(10) + char(13)

print @printDetalle
Set @Detalle = @Detalle + @printDetalle

Set @Index += 1
END

Declare @Pie As Varchar(Max) = ”

Set @Pie = ‘
IF @Inserted = 0
BEGIN
DELETE FROM dbo.AUDIT_LOG_TRANSACTIONS WHERE AUDIT_LOG_TRANSACTION_ID = @AUDIT_LOG_TRANSACTION_ID
END
— Restore @@IDENTITY Value
DECLARE @maxprec AS varchar(2)
SET @maxprec=CAST(@@MAX_PRECISION as varchar(2))
EXEC(”SELECT IDENTITY(decimal(”+@maxprec+”,0),”+@IDENTITY_SAVE+”,1) id INTO #tmp”)
End

GO
EXEC sp_settriggerorder @triggername=N”[dbo].[tr_u_AUDIT_’ + @Tabla + ‘]”, @order=N”Last”, @stmttype=N”UPDATE”
‘

–select ‘@Detalle: ‘ + @Detalle
print @Pie

/*
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AUDIT_LOG_TRANSACTIONS](
[AUDIT_LOG_TRANSACTION_ID] [int] IDENTITY(1,1) NOT NULL,
[DATABASE] [nvarchar](128) NOT NULL,
[TABLE_NAME] [nvarchar](261) NOT NULL,
[TABLE_SCHEMA] [nvarchar](261) NOT NULL,
[AUDIT_ACTION_ID] [tinyint] NOT NULL,
[HOST_NAME] [varchar](128) NOT NULL,
[APP_NAME] [varchar](128) NOT NULL,
[MODIFIED_BY] [varchar](128) NOT NULL,
[MODIFIED_DATE] [datetime] NOT NULL,
[AFFECTED_ROWS] [int] NOT NULL,
[SYSOBJ_ID] AS (object_id([TABLE_NAME])),
PRIMARY KEY CLUSTERED
(
[AUDIT_LOG_TRANSACTION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AUDIT_LOG_TRANSACTIONS] ADD DEFAULT (db_name()) FOR [DATABASE]
GO

ALTER TABLE [dbo].[AUDIT_LOG_TRANSACTIONS] ADD CONSTRAINT [DF_AUDIT_LOG_TRANSACTIONS_MODIFIED_BY] DEFAULT (‘System’) FOR [MODIFIED_BY]
GO

****************************
CREATE TABLE [dbo].[AUDIT_LOG_DATA](
[AUDIT_LOG_DATA_ID] [int] IDENTITY(1,1) NOT NULL,
[AUDIT_LOG_TRANSACTION_ID] [int] NOT NULL,
[PRIMARY_KEY_DATA] [nvarchar](1500) NOT NULL,
[COL_NAME] [nvarchar](128) NOT NULL,
[OLD_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_BLOB] [image] NULL,
[NEW_VALUE] AS (isnull(CONVERT([varchar](8000), [NEW_VALUE_LONG],0),CONVERT([varchar](8000),CONVERT([varbinary](8000),substring([NEW_VALUE_BLOB],(1),(8000)),0),0))),
[OLD_VALUE] AS (CONVERT([varchar](8000),[OLD_VALUE_LONG],0)),
[PRIMARY_KEY] AS ([PRIMARY_KEY_DATA]),
[DATA_TYPE] [char](1) NOT NULL,
[KEY1] [nvarchar](500) NULL,
[KEY2] [nvarchar](500) NULL,
[KEY3] [nvarchar](500) NULL,
[KEY4] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED
(
[AUDIT_LOG_DATA_ID] ASC
)
)
***************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Author,,Name oscar ramirez>
— Create date: <Create Date, ,>
— Description: <Description, ,>
— =============================================
ALTER FUNCTION [dbo].[fsdba_PrimaryKeyTable]
(
@Nombretabla as varchar(150)
)
RETURNS varchar(100)
AS
BEGIN

Declare @ParamCOLUMN_NAME varchar(100)

DECLARE @Lista varchar(MAX)
set @Lista = ”

SELECT @Lista = @Lista + COLUMN_NAME +’,’
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ‘.’ + QUOTENAME(CONSTRAINT_NAME)), ‘IsPrimaryKey’) = 1
AND TABLE_NAME = @Nombretabla –AND TABLE_SCHEMA = ‘Schema’

select @ParamCOLUMN_NAME = LEFT( cast(@Lista as varchar(MAX)),LEN( cast(@Lista as varchar(MAX)))-1)

return @ParamCOLUMN_NAME
END

–https://stackoverflow.com/questions/349524/sql-server-history-table-populate-through-sp-or-trigger
*/

Consejos para SSIS

Para ver explicación de cómo importar archivos texto a una tabla en SQL server mediante un SSIS

https://www.youtube.com/watch?v=sqFwTYyy5S0

 

 

Para ver video de cómo exportar varias tablas de sql a varios archivos texto mediante un SSIS

https://www.youtube.com/watch?v=QOzH2Hq2qPY

 

 

Para ver video de cómo exportar una tabla de SQL a varios archivos de texto delimitado mediante un SSIS

https://www.youtube.com/watch?v=rCoRM2Zc09E

Tratamiento a periodo

Cuando tienes un periodo puedes hacer este tratamiento para obtener los diferentes tipos de fechas, ménsula, trimestral etc..

DECLARE @PER DECIMAL(10,0)

SET @PER = ‘2010010000’

SELECT CASE
WHEN LEN(REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT))) = 6 THEN CAST( REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT))* 10 AS VARCHAR)
WHEN LEN(REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT))) = 3 THEN CAST( REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT))* 10 AS VARCHAR)
WHEN LEN(REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT))) = 1 THEN CAST( REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT))* 1000 AS VARCHAR)
ELSE CAST( REVERSE(CAST(REVERSE(MIN(@PER)) AS BIGINT)) AS VARCHAR) end + ‘ – ‘+
CASE
WHEN LEN(REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT))) = 6 THEN CAST( REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT))* 10 AS VARCHAR)
WHEN LEN(REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT))) = 3 THEN CAST( REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT))* 10 AS VARCHAR)
WHEN LEN(REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT))) = 1 THEN CAST( REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT))* 1000 AS VARCHAR)
ELSE CAST( REVERSE(CAST(REVERSE(MAX(@PER)) AS BIGINT)) AS VARCHAR) end

Rellena ceros

Función de tipo escalar, con esta función podrás rellenar ceros a la derecha

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[F_RellenaCeros] (@variable int, @longitud int)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @RESULTADO VARCHAR(10)
SET @RESULTADO=REPLICATE(‘0’,@LONGITUD – LEN(@VARIABLE))+CAST(@VARIABLE AS VARCHAR)
return (@Resultado)
END

 

La mejor Funcion (tabla) SQL server

Función de tipo tabla para convertir filas  de datos separadas por comas a tablas

/****** Object: UserDefinedFunction .[F_Split] Script Date: 09/01/2016 08:37:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [DBO].[F_Split](@Cadena VARCHAR(max), — Especifica una cadena así 01,02,03,04
@Delimitador VARCHAR(1) = ‘,’) — Especifica el delimitador que utiliza la @Cadena (,)
RETURNS @Lista TABLE (id int identity primary key,elemento VARCHAR(100) )
BEGIN
DECLARE @tempElemento VARCHAR(max)
WHILE CHARINDEX(@Delimitador,@Cadena,0) <> 0
BEGIN
SELECT @tempElemento = RTRIM(LTRIM(SUBSTRING(@Cadena,1,CHARINDEX(@Delimitador,@Cadena,0)-1))), @Cadena=RTRIM(LTRIM(SUBSTRING(@Cadena,CHARINDEX(@Delimitador,@Cadena,0)+LEN(@Delimitador),LEN(@Cadena))))
IF LEN(@tempElemento) > 0 –and charindex(‘_’,@tempElemento) = 0
INSERT INTO @Lista SELECT @tempElemento
END
IF LEN(@Cadena) > 0
INSERT INTO @Lista SELECT @Cadena — Put the last item in
RETURN
END

 

Exclusive access could not be obtained because the database is in use.

DECIMALES: Agrega decimales

Agrega los decimales a un numero SQL server

ALTER FUNCTION [dbo].[fn_NumDec]
(@Numero numeric(20,6),
@ND int)
RETURNS varchar(50)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @NumDec VARCHAR(50)
SELECT @NumDec = RTRIM(SUBSTRING(CONVERT(CHAR(20),ROUND(@Numero, @ND)), 1, CHARINDEX(‘.’, ROUND(@Numero, @ND)) + @ND))
RETURN(@NumDec)
END

Numeros a letras

convertir numeros a letras en sql server

 

ALTER function [dbo].[fn_Num_A_Letras]
(@Numero numeric(28,2))
RETURNS Varchar(500)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @lnEntero BIGINT,
@lcRetorno VARCHAR(512),
@lnTerna INT,
@lcMiles VARCHAR(512),
@lcCadena VARCHAR(512),
@lnUnidades INT,
@lnDecenas INT,
@lnCentenas INT,
@lnFraccion INT,
@Num2Letras VARCHAR(1000),
@NombreMoneda VARCHAR(30),
@Abreviacion VARCHAR(10),
@Simbolo VARCHAR(5),
@Fraccion VARCHAR(25)

SELECT @lnEntero = CAST(@Numero AS BIGINT),
@lnFraccion = (@Numero – @lnEntero) * 100,
@lcRetorno = ”,
@lnTerna = 1

SELECT @NombreMoneda = Nombre, @Abreviacion = Abreviacion, @Simbolo = Simbolo, @Fraccion = Fraccion
FROM MonedaPropuesta
WHERE Indice = 0

WHILE @lnEntero > 0
BEGIN /* WHILE */
— Recorro columna por columna
SELECT @lcCadena = ”
SELECT @lnUnidades = @lnEntero % 10
SELECT @lnEntero = CAST(@lnEntero/10 AS BIGINT)
SELECT @lnDecenas = @lnEntero % 10
SELECT @lnEntero = CAST(@lnEntero/10 AS BIGINT)
SELECT @lnCentenas = @lnEntero % 10
SELECT @lnEntero = CAST(@lnEntero/10 AS BIGINT)

— Analizo las unidades
SELECT @lcCadena =
CASE /* UNIDADES */
WHEN @lnUnidades = 1 THEN ‘UN ‘ + @lcCadena
WHEN @lnUnidades = 2 THEN ‘DOS ‘ + @lcCadena
WHEN @lnUnidades = 3 THEN ‘TRES ‘ + @lcCadena
WHEN @lnUnidades = 4 THEN ‘CUATRO ‘ + @lcCadena
WHEN @lnUnidades = 5 THEN ‘CINCO ‘ + @lcCadena
WHEN @lnUnidades = 6 THEN ‘SEIS ‘ + @lcCadena
WHEN @lnUnidades = 7 THEN ‘SIETE ‘ + @lcCadena
WHEN @lnUnidades = 8 THEN ‘OCHO ‘ + @lcCadena
WHEN @lnUnidades = 9 THEN ‘NUEVE ‘ + @lcCadena
ELSE @lcCadena
END /* UNIDADES */

— Analizo las decenas
SELECT @lcCadena =
CASE /* DECENAS */
WHEN @lnDecenas = 1 THEN
CASE @lnUnidades
WHEN 0 THEN ‘DIEZ ‘
WHEN 1 THEN ‘ONCE ‘
WHEN 2 THEN ‘DOCE ‘
WHEN 3 THEN ‘TRECE ‘
WHEN 4 THEN ‘CATORCE ‘
WHEN 5 THEN ‘QUINCE ‘
ELSE ‘DIECI’ + @lcCadena
END
WHEN @lnDecenas = 2 AND @lnUnidades = 0 THEN ‘VEINTE ‘ + @lcCadena
WHEN @lnDecenas = 2 AND @lnUnidades <> 0 THEN ‘VEINTI’ + @lcCadena
WHEN @lnDecenas = 3 AND @lnUnidades = 0 THEN ‘TREINTA ‘ + @lcCadena
WHEN @lnDecenas = 3 AND @lnUnidades <> 0 THEN ‘TREINTA Y ‘ + @lcCadena
WHEN @lnDecenas = 4 AND @lnUnidades = 0 THEN ‘CUARENTA ‘ + @lcCadena
WHEN @lnDecenas = 4 AND @lnUnidades <> 0 THEN ‘CUARENTA Y ‘ + @lcCadena
WHEN @lnDecenas = 5 AND @lnUnidades = 0 THEN ‘CINCUENTA ‘ + @lcCadena
WHEN @lnDecenas = 5 AND @lnUnidades <> 0 THEN ‘CINCUENTA Y ‘ + @lcCadena
WHEN @lnDecenas = 6 AND @lnUnidades = 0 THEN ‘SESENTA ‘ + @lcCadena
WHEN @lnDecenas = 6 AND @lnUnidades <> 0 THEN ‘SESENTA Y ‘ + @lcCadena
WHEN @lnDecenas = 7 AND @lnUnidades = 0 THEN ‘SETENTA ‘ + @lcCadena
WHEN @lnDecenas = 7 AND @lnUnidades <> 0 THEN ‘SETENTA Y ‘ + @lcCadena
WHEN @lnDecenas = 8 AND @lnUnidades = 0 THEN ‘OCHENTA ‘ + @lcCadena
WHEN @lnDecenas = 8 AND @lnUnidades <> 0 THEN ‘OCHENTA Y ‘ + @lcCadena
WHEN @lnDecenas = 9 AND @lnUnidades = 0 THEN ‘NOVENTA ‘ + @lcCadena
WHEN @lnDecenas = 9 AND @lnUnidades <> 0 THEN ‘NOVENTA Y ‘ + @lcCadena
ELSE @lcCadena
END /* DECENAS */

— Analizo las centenas
SELECT @lcCadena =
CASE /* CENTENAS */
WHEN @lnCentenas = 1 AND @lnUnidades = 0 AND @lnDecenas = 0 THEN ‘CIEN ‘ + @lcCadena
WHEN @lnCentenas = 1 AND NOT(@lnUnidades = 0 AND @lnDecenas = 0) THEN ‘CIENTO ‘ + @lcCadena
WHEN @lnCentenas = 2 THEN ‘DOSCIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 3 THEN ‘TRESCIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 4 THEN ‘CUATROCIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 5 THEN ‘QUINIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 6 THEN ‘SEISCIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 7 THEN ‘SETECIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 8 THEN ‘OCHOCIENTOS ‘ + @lcCadena
WHEN @lnCentenas = 9 THEN ‘NOVECIENTOS ‘ + @lcCadena
ELSE @lcCadena
END /* CENTENAS */

— Analizo los millares
SELECT @lcCadena =
CASE /* TERNA */
WHEN @lnTerna = 1 THEN @lcCadena
WHEN @lnTerna = 2 AND (@lnUnidades + @lnDecenas + @lnCentenas <> 0) THEN @lcCadena + ‘ MIL ‘
WHEN @lnTerna = 3 AND (@lnUnidades + @lnDecenas + @lnCentenas <> 0) AND
@lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0 THEN @lcCadena + ‘ MILLON ‘
WHEN @lnTerna = 3 AND NOT (@lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0) THEN @lcCadena + ‘ MILLONES ‘
WHEN @lnTerna = 4 AND @lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0 THEN @lcCadena + ‘ MIL ‘
WHEN @lnTerna = 4 AND NOT (@lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0) THEN @lcCadena + ‘ MIL ‘
WHEN @lnTerna = 5 AND NOT (@lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0) THEN @lcCadena + ‘ BILLONES ‘
ELSE ”
END /* MILLARES */

— Armo el retorno columna a columna
SELECT @lcRetorno = @lcCadena + @lcRetorno
SELECT @lnTerna = @lnTerna + 1

END /* WHILE */

IF @lnTerna = 1
SELECT @lcRetorno = ‘CERO’

SELECT @Num2Letras = RTRIM(@lcRetorno) + ‘ ‘ + @NombreMoneda + ‘ ‘ + CASE WHEN LEN(LTRIM(STR(@lnFraccion,2))) = 1 THEN ‘0’ + LTRIM(STR(@lnFraccion,2))
ELSE LTRIM(STR(@lnFraccion,2)) END + @Fraccion + ‘ ‘ + @Abreviacion

RETURN(@Num2Letras)
END

COMO ENCONTRAR UN TEXTO DENTRO DE UN PROCEDIMIENTO ALMACENADO O TRIGGER EN UNA BASE DE DATOS

buscar en procedimientos almacenados sql server

Para realizar una búsqueda de un texto dentro de un procedimiento almacenado o de un trigger en una base de datos de Sql, basta con hacer una consulta a la sql_modules y en el campo “definition” hacer el filtro del texto. El siguiente es un ejemplo de como realizar la consulta, basta con  reemplazar %Service%’ por el texto que requiera.

SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition LIKE ‘%Service%’ ORDER BY 2,1

 

fuente:http://www.tucodigofuente.com/2011/10/04/como-encontrar-un-texto-dentro-de-un-procedimiento-almacenado-o-trigger/

Lapso de tiempo SQL Server

Para obtener lapso de tiempo

select HORA_ENTRADA,HORA_SALIDA, DATEDIFF(HH,HORA_ENTRADA,HORA_SALIDA),
-1*(DATEDIFF(HH,HORA_ENTRADA,HORA_SALIDA) * 60 – DATEDIFF(MI,HORA_ENTRADA,HORA_SALIDA)) from Tabla