1 0 Tag Archives: tsql
post icon

TSQL – How to find missing or deleted table identity autoincrement values

In qualità di database designer, spesso creo la chiave primaria come identity autoincrement sulle tabelle del database ed alcune volte mi è capitato di dover trovare gli ID che sono stati cancellati, per cui interrompono la sequenza dell’identity autoincrement su campo chiave.
Per fare questo si può semplicemente creare una sequenza che va da 1 (oppure 0 in base al RESEED impostato) al MAX ID presente sulla tabella e, successivamente, mettere in LEFT JOIN questa sequenza con la tabella originaria.

Di seguito lo snippet per recuperare gli ID di un campo identity autoincrement che interrompono la sequenza di valori:

DECLARE @START_ID INT,  @MAXID INT

SET @START_ID = 1
SET @MAXID = (SELECT MAX(ID) FROM [TABLENAME_TO_CHECK])

DECLARE @SEQUENCE_TABLE TABLE (ID BIGINT)

--CREATE TABLE WITH ID FROM [@START_ID] TO [MAX TABLE ID]
WHILE @START_ID < @MAXID
	BEGIN
		INSERT INTO @SEQUENCE_TABLE VALUES(@START_ID)

		SET @START_ID = @START_ID + 1
	END

SELECT S.ID AS [MISSING ID] 
FROM @SEQUENCE_TABLE S 
		LEFT JOIN [TABLENAME_TO_CHECK] AS T ON  S.ID = T.ID
WHERE T.ID IS NULL
post icon

TSQL – Passing a list to a Stored Procedure

 

Per diverse esigenze, spesso mi è capitato di dover inviare una lista di valori ad una Stored Procedure.
Per raggiungere questo obiettivo è possibile escogitare un workaround mediante la funzione TSQL nodes(XQuery) sui tipi di dato XML.

Vediamo due esempi per comprendere al meglio la soluzione proposta.

Esempio 1: Come inviare ad una Stored Procedure una lista di ID.

1 – Costruisco un XML che contiene la lista degli ID, come di seguito:

 
<MyIDList>
  <ID>1</ID>
  <ID>2</ID>
</MyIDList>

In C# posso costruire l’XML nel modo seguente:

XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;
		
//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyIDList");
xmlDoc.AppendChild(xRoot);
		
//Add ID 1
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "1";
xRoot.AppendChild(xElem);
//Add ID 2
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "2";
xRoot.AppendChild(xElem);

2 – Invio alla Stored Procedure l’XML costruito.

In C# creo un DbParam come di seguito:

//@MyIDListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyIDListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml;  //Nota passo l'InnerXml
dbCommand.Parameters.Add(dbParam);		

3 – Recupero dal parametro @MyIDListXML la lista di ID in formato relazionale per poter effettuare qualsiasi tipo di operazione (JOIN, WHERE, etc.)

SELECT T.Item.value('.' , 'BIGINT') AS ID
FROM @MyIDListXML.nodes('//MyIDList/ID') AS T(Item)

Il risultato sarà una tabella con due valori, come mostrato in figura:

Esempio 2: Come inviare una tabella ad una Stored Procedure.

1 – Costruisco un XML che rappresenta la tabella da inviare:

 
<MyList>
	<item attributo1="value11" attributo2="value12" />
	<item attributo1="value21" attributo2="value22" />
</MyList>

In C# posso contruire l’XML nel modo seguente:

 
XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;

//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyList");
xmlDoc.AppendChild(xRoot);

//add item 1
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value11");
xElem.SetAttribute("attributo2", "value12");
xRoot.AppendChild(xElem);

//add item 2
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value21");
xElem.SetAttribute("attributo2", "value22");
xRoot.AppendChild(xElem);

2 – Invio alla Stored Procedure l’XML costruito.

In C# creo un DbParameter come di seguito:

 
//@MyListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml;  //Nota passo l'InnerXml
dbCommand.Parameters.Add(dbParam);		

3 – Recupero dal parametro @MyListXML la lista di ID in formato relazionale per poter effettuare qualsiasi tipo di operazione (JOIN, WHERE, etc.)

 
SELECT T.Item.value('@attributo1' , 'varchar(10)') AS Attributo1,
	T.Item.value('@attributo2' , 'varchar(10)') AS Attributo2
FROM @MyListXML.nodes('//MyList/item') AS T(Item)

Il risultato sarà una tabella con i valori come mostrato in figura:

E’ possibile inviare qualsiasi struttura XML e lavorare lato SQL Engine con la funzione nodes(XQuery).

26 aprile 2013 @ 15:43
post icon

T-SQL rename Table Sql Server

Quando si effettuano modifiche alla struttura di un database Sql Server con Microsoft SQL Server Management Studio è possibile ottenere facilmente lo script mediante la funzione Generate Change Script.

Questo non accade quando si rinomina un oggetto del database, ad esempio quando si rinomina una tabella (F2) o una Stored Procedure, ma anche una quandi si rinomina la colonna di una tabella e così via.

In questi casi è possibile utilizzare la stored procedure di sistema sp_rename (Transact-SQL).

Di seguito alcuni esempi di utilizzo:

Rinominare una tabella del database:

EXEC sp_rename 'Vecchio_Nome_Tabella', 'Nuovo_Nome_Tabella';

Rinominare una colonna di una tabella del database:

EXEC sp_rename 'Nome_Tabella.Vecchio_Nome_Colonna', 'Nuovo_Nome_Colonna', 'COLUMN';

Si applica a SQL Server 2005 / 2008.

16 settembre 2011 @ 15:47
post icon

SQL Split String

Spesso mi è capitato di dover effettuare uno split di una stringa in sql.
Dato che non esiste una funzione di sistema per effettuare lo split bisogna creare una User Defined Function che restituisce una tabella con gli elementi risultanti dallo split.

La UDF in SQL per lo split di una stringa è definita di seguito:

USE [Northwind]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitString]') 
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SplitString]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[SplitString](@StringToSplit nvarchar (4000), @Separ nvarchar (10))

RETURNS @ValueTable TABLE ([Elem] nvarchar(4000))
BEGIN
	DECLARE @NextString nvarchar(4000)
	DECLARE @Pos int
	DECLARE @NextPos int
	DECLARE @CommaCheck nvarchar(1)

	--Inizialize
	SET @NextString = ''
	SET @CommaCheck = right(@StringToSplit,1)
	SET @StringToSplit = @StringToSplit + @Separ

	--Get the position of the first @Separ
	SET @Pos = CHARINDEX(@Separ,@StringToSplit)
	SET @NextPos = 1

	--Loop while there is still a comma in the String of levels
	WHILE (@pos <>  0)  
	BEGIN
		SET @NextString = RTRIM(LTRIM(SUBSTRING(@StringToSplit,1,@Pos - 1)))

		IF(@NextString <> '')
			BEGIN
				INSERT INTO @ValueTable ([Elem]) Values (@NextString)
			END

		SET @StringToSplit = SUBSTRING(@StringToSplit,@pos +1,len(@StringToSplit))

		SET @NextPos = @Pos
		SET @pos  = CHARINDEX(@Separ,@StringToSplit)
	END

	RETURN
END
GO

SELECT * from [dbo].[SplitString]('Test;split;string;', ';')

Il risultato della select in coda allo script è il seguente:

Download source split_string_sql.zip (736,00 bytes)

24 febbraio 2009 @ 13:44