One parameter, list of values

/*
Target:
1) Develop a stored procedure where sending a list of values ​​concatenated for space,
bulk insert all records
2) Develop a procedure that receives a list of occurrences can be concatenated by space
bring the possible values

Objetivo:
1) Desarrollar un procedimiento almacenado donde enviando una lista de valores concatenados por espacio,
inserte de forma masiva todos los registros
2) Desarrollar un procedimiento que enviando una lista de ocurrencias concatenadas por espacio puedan
traer los valores posibles

*/




CREATE FUNCTION dbo.fn_ParametersToTable( @ParametrosIn VARCHAR(max))
RETURNS @ParameterTable TABLE ( ParameterValue varchar(50))
AS
BEGIN
/*
Function that can process a parameter and perform a split of the values ​​returned in a table
Funcion que permite procesar un parametro y realizar un split de los valores devolviendolos en una tabla
*/
DECLARE @ParamItem varchar(50)
WHILE CHARINDEX( ' ', @ParametrosIn ) > 0 BEGIN
SET @ParamItem = LOWER( LTRIM(RTRIM( SUBSTRING( @ParametrosIn , 1, CHARINDEX( ' ', @ParametrosIn ) -1))))
SET @ParametrosIn = SUBSTRING( @ParametrosIn , CHARINDEX( ' ', @ParametrosIn ) + 1, len(@ParametrosIn))
IF NOT EXISTS ( SELECT * FROM @ParameterTable WHERE ParameterValue = @ParamItem)
INSERT @ParameterTable ( ParameterValue ) VALUES (@ParamItem)
END
IF LEN(rtrim(@ParametrosIn) ) != '' BEGIN
SET @ParamItem = LOWER( LTRIM(RTRIM( @ParametrosIn )))
IF NOT EXISTS ( SELECT * FROM @ParameterTable WHERE ParameterValue = @ParamItem)
INSERT @ParameterTable ( ParameterValue ) VALUES (@ParamItem )
END
RETURN
END
GO


/*
Sample table to demonstrate the use
Tabla de muestra para demostrar el uso
*/
CREATE TABLE ExampleTbl (field1 int, field2 varchar(20))






GO

CREATE PROCEDURE ExampleInsertList
@ValueList varchar(max)
AS
DECLARE @field1 INT
DECLARE @field2 VARCHAR(20)
/*
Stored procedure taking a single parameter, perform a stock split and inserted into a table

Procedimiento almacenado tomando un solo parametro, realiza un split de valores e inserta en una tabla
*/


INSERT INTO
ExampleTbl
(
field1 ,
field2
)
SELECT
CONVERT(INT, ParameterValue) AS field1,
'Value ' + ParameterValue AS field2
FROM
dbo.fn_ParametersToTable(@ValueList )
GO

/*
Run the test where you insert all the values ​​listed in Table ExampleTbl

Ejecutamos la prueba donde se inserta todos los valores de la lista en la tabla ExampleTbl
*/

EXEC ExampleInsertList @ValueList = '1 2 3 4 5 6 7 8 9 10 11 12 232 4545 125454 5645646 465465 4521 321 2138574 54'

GO

CREATE PROCEDURE ExampleQueryList
@ParameterList VARCHAR(MAX)
AS

/*

Consultation where a list of parameters is the conditional search

Consulta donde una lista de parametros es el condicional de busqueda

*/
SELECT ExampleTbl.* FROM
ExampleTbl
WHERE
field1
IN (
SELECT
CONVERT(INT, ParameterValue)
FROM
dbo.fn_ParametersToTable(@ParameterList )
)

GO

/*
Query Execution of example with lists

Ejecucion del ejemplo de Query con listas
*/
EXEC ExampleQueryList @ParameterList = '1 12 232'


/*

They now have a circuit with all the suggestions of using the function fn_ParametersToTable if your taste and utility can donate what he deems necessary and will be rewarded in many more post

Ahora tienen un circuito con todas las sugerencias del uso de la funcion fn_ParametersToTable si es de su gusto y utilidad puede donar lo que crea conveniente y serà retribuido en muchos mas post

*/

Database Text to NVARCHAR

/*

This time the problem we faced was: passed all existing TEXT fields in a database field NVARCHAR (MAX).

EASY, enjoy!

En esta oportunidad el problema a resolver era: pasar todos los campos TEXT existentes en una base de datos en campos NVARCHAR(MAX).

Sensillo, enjoy!

Nota: Este script puede dañar la integridad de la base, cualquier daño que provoque será bajo su responsabilidad .

Note: This script can damage the integrity of the base, causing damage thatwill be your responsibility.

*/

DECLARE @CMD VARCHAR(255)

DECLARE @TABLENAME VARCHAR(255)

DECLARE @COLUMNNAME VARCHAR(255)

--Well here declare a cursor, only that

DECLARE Table_cursor CURSOR FOR

--Make the cmd line to be executed

SELECT

'ALTER TABLE ' + O.NAME + ' ALTER COLUMN ' + C.NAME + ' NVARCHAR(MAX) NULL' AS CMD,

O.NAME AS TABLENAME,

C.NAME AS COLUMNNAME

FROM

SYS.OBJECTS AS O

INNER JOIN

SYS.COLUMNS AS C

ON (C.OBJECT_ID = O.OBJECT_ID)

INNER JOIN

SYS.TYPES AS T

ON( T.SYSTEM_TYPE_ID = C.SYSTEM_TYPE_ID )

WHERE

TYPE = 'U' AND C.SYSTEM_TYPE_ID = 35

--Open the cursor

OPEN Table_cursor

--Fetch the first

FETCH NEXT FROM Table_cursor

--Use the field values in the declared vars

INTO @CMD, @TABLENAME, @COLUMNNAME

--LOOP NOT EOF

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT REPLICATE('=',255)

PRINT 'TABLE:' + @TABLENAME

PRINT 'COLUMN:' + @COLUMNNAME

PRINT ''

PRINT 'EXECUTING => ' + @CMD

EXEC (@CMD) --here execute the command

PRINT 'DONE '

--Move next :P

FETCH NEXT FROM Table_cursor

INTO @CMD, @TABLENAME, @COLUMNNAME

--End of cursor

END

--Close an Deallocate cursor

CLOSE Table_cursor

DEALLOCATE Table_cursor

Print replicate('=',255)

DB Source control

/*

This post is a example of source control on sql databases, You can modify, extend or cut the functions of the next script, ask and discuss, The author is not responsible of content, only for educational purposes.

*/

/*Create a table where save every change on DB */

CREATE TABLE DEVSQLControl (

[ID] INT IDENTITY(1,1) ,

[Name] VARCHAR(256) ,

[Type] CHAR(2) ,

Create_date DATETIME ,

Modify_date DATETIME ,

DataCheckSum BIGINT ,

stamp TIMESTAMP ,

SnapDate DATETIME

)

/*

Insert in to DEVSQLControl table the new changes, including what I want to seek, you are free to change the list to your wish

*/

INSERT INTO

DEVSQLControl

(

[Name] ,

[type] ,

Create_date ,

Modify_date ,

DataCheckSum ,

SnapDate

)

SELECT

O.name ,

O.type ,

O.create_date ,

O.modify_date ,

BINARY_CHECKSUM(

O.name,

O.object_id,

O.principal_id,

O.schema_id,

O.parent_object_id,

O.type,

O.type_desc,

O.create_date,

O.modify_date,

O.is_ms_shipped,

O.is_published,

O.is_schema_published

) ,

Getdate()

FROM

SYS.OBJECTS AS O

INNER JOIN

DEVSQLControl AS DV

ON(-- 1 = 1

O.name COLLATE DATABASE_DEFAULT = DV.name COLLATE DATABASE_DEFAULT

AND O.type COLLATE DATABASE_DEFAULT = DV.type COLLATE DATABASE_DEFAULT

)

WHERE O.TYPE

IN ( 'U','P','fn', 'C ','PK','UQ','F ', 'D ' )

AND DataCheckSum <> BINARY_CHECKSUM(

O.name,

O.object_id,

O.principal_id,

O.schema_id,

O.parent_object_id,

O.type,

O.type_desc,

O.create_date,

O.modify_date,

O.is_ms_shipped,

O.is_published,

O.is_schema_published

)

GROUP BY

O.name ,

O.type ,

O.create_date ,

O.modify_date ,

BINARY_CHECKSUM(

O.name,

O.object_id,

O.principal_id,

O.schema_id,

O.parent_object_id,

O.type,

O.type_desc,

O.create_date,

O.modify_date,

O.is_ms_shipped,

O.is_published,

O.is_schema_published

)

Audit Script Wizard (SQL Server 2008) II

After posting my article audit Wizard where you create an additional table for each table to be audited, in this issue propose another solution which is created only once a single table and persist data in a xml field, the differences are many, but you have to discover which are the scope and mainly depends on the culture to use xml.

Best regards

See also: Audit Script Wizard (SQL Server 2008)

http://sqldimention.blogspot.com/2010/09/audit-wizard-script.html

Después de publicar mi articulo de Wizard de auditoria donde se crea una tabla adicional por cada tabla a auditar, en esta entrega propongo otra solución donde se crea una única vez una sola tabla y los datos persisten en un campo xml, las diferencias son muchas, pero hay que evaluar cuales son los alcances y sobre todo depende de la cultura a usar xml.

Saludos cordiales

Ver tambien:Audit Wizard Script (SQL server 2008)

http://sqldimention.blogspot.com/2010/09/audit-wizard-script.html

-- =============================================

-- Author: Skynet Silverstar

-- Create date:

-- Description: Script maker where you conform

-- audit info on a specific SQL table

-- =============================================

-- Creador de script donde conforma

-- Informacion de auditoria de

-- una o mas tablas especificas

-- sobre una unica tabla de auditoria

-- =============================================

DECLARE @object_name VARCHAR(50)

DECLARE @column_name VARCHAR(50)

DECLARE @is_nullable VARCHAR(50)

DECLARE @type VARCHAR(50)

DECLARE @max_length INT

DECLARE @precision INT

DECLARE @scale INT

DECLARE @Audit_name VARCHAR(255)

DECLARE @trigger_name VARCHAR(255)

-- The target table name

-- El nombre de la tabla

SET @object_name = 'factura'

--Here change the name of tables and triggers if you want

--Aqui cambie el nombre de las tablas y disparadore si usted quiere!

SET @Audit_name = 'AUDIT_Table'

SET @trigger_name = 'AUDIT_TR_' + @object_name

IF EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME IN ( @trigger_name) )

BEGIN

PRINT '---------------------------------------------------------'

PRINT '--Informe de proceso con error ATENCION!'

PRINT '---------------------------------------------------------'

PRINT '--El trigger ' + @trigger_name + ' ya existe, verificar y volver a ejecutar el proceso'

PRINT '--'

PRINT '--Sugerido hacer backup de la tabla de auditoria y base de datos'

PRINT '--copiar el resultado y descomentar los comandos siguientes y ejecutar F5'

PRINT '--DROP TRIGGER ' + @trigger_name

PRINT ''

PRINT '---------------------------------------------------------'

PRINT '--Warning!! proccess info error'

PRINT '---------------------------------------------------------'

PRINT '--Trigger ' + @trigger_name + ' already exists, Verify and run again the script'

PRINT '--'

PRINT '--Make a backup and save the information'

PRINT '--Execute the next lines if you want delete the old schema'

PRINT '--DROP TRIGGER ' + @trigger_name

END ELSE BEGIN

SET NOCOUNT ON

PRINT '-------- Script de creacion de tabla de auditoria ' + @Audit_name+ ' si existe--------'

PRINT '-------- Make the script to make the audit table ' + @Audit_name+ ' IF EXISTs--------'

PRINT '-------- AUTOR: ' + USER_NAME()

PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

PRINT 'SET ANSI_NULLS ON'

PRINT 'GO'

PRINT ''

PRINT 'SET QUOTED_IDENTIFIER ON'

PRINT 'GO'

PRINT ''

PRINT 'SET ANSI_PADDING ON'

PRINT 'GO'

PRINT ''

PRINT 'IF NOT EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME IN ( ''' + @Audit_name + ''') )'

PRINT 'BEGIN'

PRINT CHAR(9) + 'CREATE TABLE ' + @Audit_name + ' ('

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_ID] ' + CHAR(9) + ' INT IDENTITY(1,1) ' + CHAR(9) + ','

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_ACTION] ' + CHAR(9) + ' CHAR(1) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_SPID] ' + CHAR(9) + ' INT ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_DATE] ' + CHAR(9) + ' DATETIME ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_USER] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_APLICATION] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_HOST] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' NOT NULL,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_TIMESTAMP] ' + CHAR(9) + ' TIMESTAMP ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_HASH] ' + CHAR(9) + ' BIGINT ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_TABLENAME] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_XML_CONTENT] ' + CHAR(9) + ' XML ' + CHAR(9) + ' ,'

PRINT CHAR(9) + ' CONSTRAINT [' + @Audit_name + '_PK] PRIMARY KEY CLUSTERED '

PRINT CHAR(9) + ' ('

PRINT CHAR(9) + ' [AUDIT_ID] ASC'

PRINT CHAR(9) + ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'

PRINT CHAR(9) + ' ) ON [PRIMARY]'

PRINT 'END '

PRINT ' '

PRINT ' GO'

PRINT ' '

PRINT ' SET ANSI_PADDING OFF'

PRINT ' GO'

/********************************************************************************

creacion del disparador

Make a trigger

********************************************************************************/

PRINT '-------- Script de creacion de triger de auditoria AUDIT_TR_' + @object_name+ '--------'

PRINT '-------- Make a script of Audit trigger named AUDIT_TR_' + @object_name+ '--------'

PRINT '-------- AUTOR: ' + USER_NAME()

PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

PRINT 'SET ANSI_NULLS ON'

PRINT 'GO'

PRINT ''

PRINT 'SET QUOTED_IDENTIFIER ON'

PRINT 'GO'

PRINT ''

PRINT 'SET ANSI_PADDING ON'

PRINT 'GO'

PRINT ''

PRINT 'CREATE TRIGGER ' + @trigger_name + ' '

PRINT ' ON ' + @object_name + ' '

PRINT ' AFTER INSERT, UPDATE, DELETE'

PRINT ' AS'

PRINT ' BEGIN'

PRINT CHAR(9) + ' -- Verifico que tipo de accion se va a realizar'

PRINT CHAR(9) + ' -- U : almaceno el dato a actualizar'

PRINT CHAR(9) + ' -- D : almaceno el dato a ser eliminado'

PRINT CHAR(9) + ' -- I : almaceno el dato a ser insertado'

PRINT ''

PRINT CHAR(9) + ' -- Verify the type of operatio to do'

PRINT CHAR(9) + ' -- U : Save on update'

PRINT CHAR(9) + ' -- D : Save on delete'

PRINT CHAR(9) + ' -- I : save on insert'

PRINT CHAR(9) + 'DECLARE @ACTION CHAR(1)'

PRINT CHAR(9) + 'DECLARE @XMLDATA XML'

PRINT CHAR(9) + 'DECLARE @HASHDATA BIGINT'

PRINT ''

PRINT CHAR(9) + 'IF EXISTS(SELECT * FROM DELETED) AND EXISTS(SELECT * FROM INSERTED)'

PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''U'''

PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM DELETED) '

PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''D'' '

PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM INSERTED)'

PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''I'' '

PRINT ''

DECLARE @FIELDS VARCHAR(8000)

SET @FIELDS = ''

DECLARE Table_cursor CURSOR FOR

SELECT

c.name AS column_name

FROM

sys.objects AS o

INNER JOIN

sys.columns AS c

ON ( o.object_id = c.object_id)

WHERE

o.type = 'U'

AND o.name = @object_name

ORDER BY

o.name ,

column_id

OPEN Table_cursor

FETCH NEXT FROM Table_cursor

INTO @column_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FIELDS = @FIELDS + '' + ' [' + @column_name + '] ,'

FETCH NEXT FROM Table_cursor

INTO @column_name

END

CLOSE Table_cursor

DEALLOCATE Table_cursor

SET @FIELDS = substring(@FIELDS , 1 , len(@FIELDS ) - 1)

PRINT CHAR(9) + 'SET NOCOUNT ON'

PRINT CHAR(9) + 'IF (@ACTION IN ( ''I'')) BEGIN'

PRINT CHAR(9) + CHAR(9) + 'SET @XMLDATA = CAST((SELECT ' + @FIELDS + ', BINARY_CHECKSUM(' + @FIELDS + ') AS [AUDIT_HASH] FROM INSERTED FOR XML AUTO, Elements) AS XML)'

PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '

PRINT CHAR(9) + CHAR(9) + '( [AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST], [AUDIT_HASH], [AUDIT_SPID], [AUDIT_TABLENAME] , [AUDIT_XML_CONTENT]) VALUES'

PRINT CHAR(9) + CHAR(9) + '( @ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() , 0 , @@SPID, '

PRINT CHAR(9) + CHAR(9) + '''' + @object_name + ''', @XMLDATA ) '

PRINT CHAR(9) + 'END'

PRINT CHAR(9) + 'ELSE IF (@ACTION IN (''U'', ''D'')) BEGIN'

PRINT CHAR(9) + CHAR(9) + 'SET @XMLDATA = CAST((SELECT ' + @FIELDS + ', BINARY_CHECKSUM(' + @FIELDS + ') AS [AUDIT_HASH] FROM DELETED FOR XML AUTO, Elements) AS XML)'

PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '

PRINT CHAR(9) + CHAR(9) + '( [AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST], [AUDIT_HASH], [AUDIT_SPID], [AUDIT_TABLENAME] , [AUDIT_XML_CONTENT]) VALUES'

PRINT CHAR(9) + CHAR(9) + '( @ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() , 0 , @@SPID, '

PRINT CHAR(9) + CHAR(9) + '''' + @object_name + ''', @XMLDATA ) '

PRINT CHAR(9) + 'END'

PRINT 'END'

PRINT '---------------------------------------------------------'

PRINT '--Informe de proceso'

PRINT '---------------------------------------------------------'

PRINT '--Se generò sobre tabla ' + @object_name + ' el trigger ' + @trigger_name + ' '

PRINT '--'

PRINT '--Copie el contenido de resultado a un archivo. sql'

PRINT '--Nombre: xxx_creacion_auditoria_' + @object_name + '.sql'

PRINT ''

PRINT '---------------------------------------------------------'

PRINT '--Proccess info'

PRINT '---------------------------------------------------------'

PRINT '--With the target table ' + @object_name + ' made the trigger ' + @trigger_name + ' '

PRINT '--'

PRINT '--Copy the result to a new query and save '

PRINT '--Name: xxx_Create_Audit_' + @object_name + '.sql'

END