1 0 Tag Archives: tsql
post icon

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

As a database designer, I often create on database tables the primary key as identity autoincrement and sometimes I needed to find missing or deleted IDs thet interrupt the sequence of autoincrement number on the primary key.
To do this you can create a sequence from 1 (or 0 based on setted RESEED) to MAX ID available on the table and than make a LEFT JOIN of this sequence with start table.

The following snippet get the list of IDs of identity autoincrement column that interrupt the sequence of autoincrement values:

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

 

For different needs, often I had needs to send a list of values ​​to a Stored Procedure.
To achieve this goal it is possible to use a workaround with the TSQL function nodes(XQuery) on the types of XML data.

We’ll see two examples to better understand the proposed solution.

Example 1: How to send to a Stored Procedure a list of ID.

1 – Build an XML that contains a list of ID, as follows:

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

In C # I can build XML as follows:

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 – Sent the XML built to the Stored Procedure.

In C# create DbParam as follows:

//@MyIDListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyIDListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);				

3 – Get the @MyIDListXML parameter value and obtain the list of ID in a relational format to do a lot of relational operations (JOIN, WHERE, etc.)

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

The result will be a table with two value like in figure:

Example 2: Hot to send a table to a Stored Procedure.

1 – Build an XML which represents a table like the following:

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

In C# I can create the XML as follows

 
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 – Sent to the Stored Procedure the XML built

In C# create DbParameter as follows:

 
//@MyListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);	

3 – Get @MyListXML list values and I can do a lot of relational operations (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)

The result will be like in the following table:

It’s possible to pass any XML structure and work on SQL Engine with the nodes(XQuery) function.

April 26th, 2013 @ 03:43
post icon

T-SQL rename Table Sql Server

When you modify database schema on Sql Server with Microsoft SQL Server Management Studio you can simply create script with Generate Change Script function.

This is not allowed when you rename a database object, for example when you rename a table (F2 key) or rename Stored Procedure, but also when you rename a table column and so on.

In this situation you can use the system stored procedure sp_rename (Transact-SQL).

Below some utilization examples:

Rename table on database:

EXEC sp_rename 'Vecchio_Nome_Tabella', 'Nuovo_Nome_Tabella';

Rename table column on database:

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

Applies to SQL Server 2005 / 2008.

September 16th, 2011 @ 03: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)

February 24th, 2009 @ 01:44