Login Login
MORE

WIDGETS

Widgets

Wanted articles
  • No information available
Who is online?
  • Nobody is logged in.
Article tools

MSSQL:Script Utili

From Aino Wiki

Jump to: navigation, search

Trasformazioni

Da campo CSV a risultato Tabellare

Es.1

Suddivide la stringa in ingresso in n-parti corripondenti ai segmenti di stringa che finiscono per il carattere di divisione. Restituisce una Tabella. Le stringhe in tabella di output son ripulite puntualmente da spazi vuoti o caratteri di controllo. 

/*
Es:
DECLARE @txtSQL_View as VARCHAR(MAX) = 'Avaya,Patton,Huawei,Digitalk'
 
SELECT
	[Value]
FROM dbo.Split(@txtSQL_View, ',')
*/
CREATE FUNCTION [dbo].[Split](
		 @myString nvarchar (max),
		 @Delimiter nvarchar (10))	
	RETURNS @ValueTable TABLE ([Value] nvarchar(max))
BEGIN
	DECLARE	@NextString nvarchar(4000)
			, @Pos int
			, @NextPos int
			, @CommaCheck nvarchar(1)
	--Initialize
	SET @NextString = ''
	SET @CommaCheck = right(@myString,1) 
	--Check for trailing Comma, if not exists, INSERT
	--if (@CommaCheck <> @Delimiter )
	SET @myString = @myString + @Delimiter
 
	--Get position of first Comma
	SET @Pos = charindex(@Delimiter,@myString)
	SET @NextPos = 1
 
	--Loop while there is still a comma in the String of levels
	WHILE (@pos <>  0)  
	BEGIN
		SET @NextString = LTrim(RTrim(
							REPLACE(
								REPLACE(
									REPLACE(SUBSTRING(@myString,1,@Pos - 1),
										CHAR(9), ''),	-- Tabulazione
										CHAR(10), ''),	-- Inizio riga
										CHAR(13), '')	-- Ritorno a capo
								))
		INSERT INTO @ValueTable ([Value]) 
			Values 
		(@NextString)
		SET @myString = SUBSTRING(@myString, @pos +1, len(@myString))
 
		SET @NextPos = @Pos
		SET @pos  = charindex(@Delimiter,@myString)
	END
	RETURN
END

Es.2

Da Stack Overflow. Si utilizza la funzione prima creata da richiamare in una query su ciascun record col campo CSV: 

---Come funziona la funzione tabellare:
DECLARE @txtSQL_View as VARCHAR(MAX) = 'Avaya,Patton,Huawei,Digitalk'
SELECT
	[Value]
FROM dbo.Split(@txtSQL_View, ',')
 
--Applicazione della funzione tabellare su ciascuna riga
--di una tabella che ha un campo [System] che ha valori CSV
--il risultato finale è una estrazione dei singoli valori CSV
--in formato tabellare
SELECT 
	T.[System], P.[Value]
FROM			dbo.[Task$]					AS T
CROSS APPLY		dbo.Split(T.[System], ',')	AS P
WHERE T.[System] LIKE '%,%'

Viceversa, da selezione vettoriale a singolo campo CSV

Si analizza il caso in cui si voglia una stringa CSV i cui elementi separati da virgola sono composti da una SELECT su un singolo campo (NON si vuole usare un cursore). Code da stackoverflow.com 

DECLARE @result nvarchar(max)
SET @result = ''
 
SELECT @result = @result + [Column] + N','
FROM [TABLE]
 
PRINT @result

Lo svantaggio di questa solizione è che se volessimo questa stringa CSV come nuovo campo di una SELECT non potremmo usare la tecnica della variabile che si compone ad ogni ciclo.

Es. 1

Si crea la seguente fuznione che sarà utilizzabile anche in una QUERY superando il problema esposto prima, nella segeunte funzione si risolve anche il problema per cui nella selezione destinata al campo CSV ci posson esser valori DUPLICATI. I duplicati si eleiminano adottando una DITINCT dopo aver ritrasformato la stringa CSV in tabella vettoriale e poi riapplicando la funzionalità di partenza.
La funzione non è generalizzata ma applicata su una tabella e campo specifico. 

CREATE FUNCTION [dbo].[ListActivityResourceNameCSV]
(
    @IDProject	AS int
)
RETURNS nvarchar(MAX)
AS
BEGIN
	DECLARE @Delimiter nvarchar (10) = ',',
			@Result NVARCHAR(MAX) = '',
			@Result_OUT NVARCHAR(MAX) = '' 
	SELECT 
		--Fa pulizia in stringhe come questa:	
		--	Garbin[45%],Roccella[45%],Bosoni[10%]
		--Lascia solo in nomi separatida virgola
		@Result = @Result 
			+ REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
			  (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
					[OwnersCSV]
				, 'TBD', '')
				, '[', '')
				, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', '')
				, '6', ''), '7', ''), '8', ''), '9', ''), '%', '')
				, ']', '')
			+ @Delimiter
	FROM [dbo].[Activities]
	WHERE [IDProject] = @IDProject
		AND [OwnersCSV] <> 'TBD'
 
	IF LEN(@Result) > 1
	BEGIN
		SET @Result = SUBSTRING(@Result, 1, LEN(@Result) - 1)
		--------------------------
		--Rimozione dei duplicati
		--------------------------
		SELECT
			@Result_OUT = @Result_OUT + [Value] + @Delimiter
		FROM	(SELECT DISTINCT 
				[Value]
			FROM [dbo].[Split](@Result, @Delimiter)
			WHERE ISNULL([Value], '') <> ''
			) AS X
		SET @Result_OUT = SUBSTRING(@Result_OUT, 1, LEN(@Result_OUT) - 1)
	END 
	RETURN @Result_OUT
END

Estrarre elemento da stringa

Il seguente script è stato copiato da [1] che è estratto dal lavoro di Aaron Bertrand sqlperformance
Data una stringa CSV con un dato separatore estrae la sottostringa alla posizione indicata.
Notare che usa una CTE (Common Table Expression) ovvero una specie di tabella in memoria Microsoft. 

/* =============================================
 
Es.:
SELECT dbo.GetSplitString_CTE('w;wait for digit;;;-2;01;;-2;', ';', 2)	
 
--Restituisce:     "wait for digit"
=============================================*/
CREATE FUNCTION [dbo].[GetSplitString_CTE]
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(4000)
AS
BEGIN
 
   DECLARE @result varchar(4000)
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
                          Item VARCHAR(4000)
                         )  
 
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);  
 
   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) >= 0        --Se si vogliono saltare gli elementi vuoti impostare    >0
   OPTION (MAXRECURSION 0);
 
   SELECT @result=Item
   FROM @Items
   WHERE position=@ElementNumber
 
   RETURN @result;
END

Cercare Testo/Campi in Oggetti del DB

In Tabelle, Viste, StoreProcedure, Trigger

Gli esempi seguenti semplicemente interrogano, mediante query SQL, le tabelle di sistema.Lo script seguente ricerca testo nei commenti delle entità del sistema.
SELECT DISTINCT
	OBJECT_SCHEMA_NAME(id) AS [SCHEMA],
	OBJECT_NAME(id) AS NomeObj, 
		OBJECTPROPERTY(id, 'IsView') IsView, 
		OBJECTPROPERTY(id, 'IsTable') IsTable, 
		OBJECTPROPERTY(id, 'IsProcedure') IsProcedure,
		OBJECTPROPERTY(id, 'IsTrigger') IsTrigger
FROM syscomments 
WHERE [text] LIKE '%aino%' and 
			[text] LIKE '%html%'
--	AND OBJECTPROPERTY(id, 'IsProcedure') = 1  -- <----------- COMMENTARE X CERCARE OVUNQUE!
--GROUP BY OBJECT_NAME(id)
order by NomeObj

La seguente presenta le info in modo più organizzato e cerca tra i nomi degli oggetti. Ad esempio per cercare tra tutti i nomi dei campi delle tabelle: 

SELECT DISTINCT SO.NAME, SO.TYPE
		, (CASE	WHEN SO.TYPE = 'F' THEN 'Chiave esterna'
				WHEN SO.TYPE = 'K' THEN 'Chiave primaria'
				WHEN SO.TYPE = 'P' THEN 'Store Procedure'
				WHEN SO.TYPE = 'S' THEN 'Tabella di Sistema'
				WHEN SO.TYPE = 'U' THEN 'Tabella'
				WHEN SO.TYPE = 'V' THEN 'Vista'
				WHEN SO.TYPE = 'FN' THEN 'Funzione utente'
				ELSE 'Sconosciuto: ' + SO.TYPE
		   END) AS TIPO
		 , SC.name AS nomeCampo, SC.xtype, TIPI.name, SC.LENGTH
 
   FROM			SYSOBJECTS SO (NOLOCK)
   INNER JOIN	SYSCOLUMNS SC (NOLOCK)	ON SO.id = SC.id
   INNER JOIN	systypes TIPI ON SC.xtype = TIPI.xtype
   WHERE SO.TYPE NOT IN ('D', 'F', 'K', 'IT', 'S', 'FN', 'P', 'SQ')
   AND SC.name LIKE '%type%'
   ORDER BY SO.TYPE, SO.Name

In Job

SELECT j.name 
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS 
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%gettotaltrafficfortime%'
  );

Esecuzione query dinamica SQL

Ci sono due alternative:

  • Usare lo statement: EXEC (@StringaConLaQuerySQL)
  • Usare la StoredProcedure di sistema: sp_executesql

Usando EXEC

Il seguente è un esempio di esecuzione di una query SQL 'montata' in una variabile stringa ed eseguita con l'istruzione EXEC, la particolarità è che quest'esecuzione produce un resultset inviato direttamente ad una INSERT in una tabella temporanea dichiarata con la seguente istruzione
DECLARE @tempProducts1 TABLE	
(	
	IDProduct INT  
)
Esempio completo:
/* Es. uso:

[dbo].[FeatureFilter_GetCount] 622, 'HW', 'LIST-UF', 0, NULL, '', '', ''*/
ALTER PROCEDURE [dbo].[FeatureFilter_GetCount]
	@categoryId			AS int,
	@idApplication		AS char(2),
	@salePriceListId	AS char(7),		--NOT NULL
	@minPrice			AS decimal(11,2) = NULL,
	@maxPrice			AS decimal(11,2) = NULL,
	@sqlBrands			AS VARCHAR(1000),
	@sqlFeatures		AS VARCHAR(2000),
	@sqlGenerics		AS VARCHAR(1000)
AS
BEGIN
	SET @minPrice = ISNULL(@minPrice, 0)
	SET @maxPrice = ISNULL(@maxPrice, 999999)	
 
	DECLARE @sql VARCHAR(MAX)
	DECLARE @tempProducts1 TABLE	
	(	
		IDProduct INT  
	)
 
	-- TABELLA CHE CONTIENE PRODOTTI CHE RISPETTANO I FILTRI ESTERNI (NO FEATUREID CORRENTE)
	SET @sql = 'SELECT DISTINCT P.IDProduct
					FROM Products AS P WITH(NOLOCK)
						INNER JOIN	ProductsRelations AS PR	WITH(NOLOCK)    
										ON P.IDProduct = PR.ProductID 
											AND PR.ApplicationID = ''' + @idApplication + '''
											AND PR.CategoryID = ' + CAST(@categoryId AS VARCHAR(10)) + '
											AND PR.IsPrimary = 1
						INNER JOIN	ProductsStoreData    AS SD	WITH(NOLOCK)    
							ON P.IDProduct = SD.ProductID 
						INNER JOIN	ProductsPublications    AS PU	WITH(NOLOCK)    
											ON P.IDProduct = PU.ProductID 
												AND PU.ApplicationID = ''' + @idApplication + '''
												AND PU.ProductStatusID = ''P'' 
						LEFT JOIN	ProductFeaturesValues AS PFV WITH(NOLOCK) 
							ON P.IDProduct = PFV.ProductID
								AND (PFV.Value <> '''' AND PFV.Value <> ''-'')
					WHERE 0 = 0 '
 
	INSERT INTO @tempProducts1 EXEC (@sql + @sqlBrands + @sqlFeatures + @sqlGenerics)
 
	SELECT Count(IDProduct) 
	FROM		@tempProducts1		AS P
	INNER JOIN	SalePrices			AS SP	WITH(NOLOCK)    ON P.IDProduct = SP.ProductID
																			AND SP.ApplicationID = @idApplication
																			AND SP.SalePriceListID = @salePriceListId
	WHERE SP.FinalTaxedPrice BETWEEN @minPrice AND @maxPrice
 


END

SP: sp_executesql

ATTENZIONE la query SQL va in un campo nvarchar. Segue esempio con parametro di input. 

DECLARE @sqlCommand nvarchar(1000),
        @columnList varchar(75),
        @city varchar(75)
 
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
 
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Esempio con paramentro intero in uscita: 

DECLARE @CountRecords int,
 @SQLQuery nvarchar(1000),
 @params nvarchar(1000);
 
SET @params = N'@i_out int OUT';
SET @SQLQuery    = N'SELECT @i_out = 5';
 
EXEC sp_executesql @SQLQuery, @params, @i_out = @CountRecords OUT;
 
SELECT @CountRecords;

Esempio con 2 paramentri in uscita: 

DECLARE @SQLQuery nvarchar(1000),
        @JobTrafficLight	AS bit,	
        @DestTableNameRule	AS nvarchar(250) = '';
 
SET @SQLQuery = 'SELECT TOP 1
		@JobTrafficLight_OUT = JobTrafficLight,
		@DestTableNameRule_OUT = DestTableNameRule
FROM ' + @DBControlName + '.[dbo].[Setup] (NOLOCK)
WHERE [Namespace] = ''' + @Namespace + '''
	AND [LnkSrvName] = ''' + @LnkSrvName + '''
	AND [Database] = ''' + @DestDBName + ''''
	EXEC sp_executesql @SQLQuery, N'@JobTrafficLight_OUT bit OUT, @DestTableNameRule_OUT nvarchar(250)  OUT'
		, @JobTrafficLight_OUT = @JobTrafficLight OUT, @DestTableNameRule_OUT = @DestTableNameRule OUT

Uso di Tabelle Temporanee

Es.1

Dichiarazione:
    DECLARE @temp TABLE
    (
        ProductFeatureID VARCHAR(10),
        Name NVARCHAR(200),
        Ranking INT
    )
Assegnazione con INSERT: 
    INSERT INTO @temp 
        (ProductFeatureID, Name, Ranking) 
    VALUES 
        ('brand', 'Marca', 0)

Esempio di cursore emulato

Cursore canonico: qui
Nel seguente esempio si usa una struttura simile a quella del cursore senza fare uso delle strutture canoniche il vantaggio è una maggiore efficienza. Si adotta una tabella temporanea dotata di campo con id automatico, si cicla su ogni suo record usando un contatore.
NOTARE:

  • l'operazione di INSERT con reperimento dell'ID appena creato in tabella usando SCOPE_IDENTITY()
  • uso della Transazione mediante il costrutto BEGIN TRANSACTION, COMMIT e IF @@ERROR <> 0 ROLLBACK 
DECLARE @counter INT
 
DECLARE @temp TABLE 
( 
    Identity_field INT IDENTITY, 
    IDSynchronization CHAR(6),
    ProductCode VARCHAR(50),
    ProducerCode VARCHAR(50),
    Name NVARCHAR(255),
    Availability DECIMAL(10, 1),
    Arrival DECIMAL(10, 1)
) 
 
INSERT INTO @temp
    SELECT TOP 500 
            L.SynchronizationID,
            L.ProductCode, L.ProducerCode, L.Name, 
            D.Availability, 
            D.Arrival
    FROM       MwImport..Limbo              AS L   (NOLOCK) 
    INNER JOIN MwImport..ProductsMapping    AS PM  (NOLOCK)   ON PM.ProductCode = L.ProductCode 
                                                                 AND PM.SynchronizationID = L.SynchronizationID 
    INNER JOIN MwImport..ProductsData       AS D   (NOLOCK)   ON D.ProductCode = L.ProductCode 
                                                                 AND D.SynchronizationID = L.SynchronizationID
    LEFT JOIN  MwImport..PictureDownloads   AS PIC (NOLOCK)   ON PM.ProductCode = PIC.ProductCode             --Xkè  LEFT Join? tanto nessuna sua info è usata!
                                                                 AND PM.SynchronizationID = PIC.SynchronizationID
    INNER JOIN MwCommerce..Brands           AS B   (NOLOCK)   ON PM.BrandID = B.IDBrand
    INNER JOIN MwCommerce..StorageCategories AS SC (NOLOCK)   ON PM.CategoryID = SC.IDCategory
    INNER JOIN MwCommerce..SuppliersSynchronizations AS SS (NOLOCK)   ON PM.SynchronizationID = SS.SynchronizationID 
                                                                AND SS.RelationTypeID = 'P'    
    WHERE L.StatusID = 'MWC'
            AND PM.MwCommerceID = 0
 
SET @counter = 1
 
WHILE (@counter <= (SELECT MAX(Identity_field) 
					FROM @temp))    
    BEGIN
       SELECT 
            @idSynchronization = IDSynchronization,
            @productCode = ProductCode,
            @producerCode = ProducerCode,
            @name = Name,
            @availability = Availability,
            @arrival = Arrival
        FROM @temp  
        WHERE Identity_field = @counter
 
        BEGIN TRANSACTION
 
			INSERT INTO Products (ProducerCode, Name, Description, TechnicalDescription, 
						BrandID, EAN, UPC, 
						CreationDate, ModifyDate) 
			VALUES 
					(@producerCode, @name, @description, @technicalDescription, 
					@idBrand, @ean, @upc, 
					GETDATE(), GETDATE())
 
			SET @idProduct = SCOPE_IDENTITY()
 
			IF @@ERROR <> 0
				BEGIN
					ROLLBACK
					SET @idProduct = 0
					RETURN
				END
 
        COMMIT
 
        SET @counter = @counter + 1
    END

Pseudo Copia di una tabella

INSERT INTO cars_cont_tbp_contratti 
   (--campi che si devono copiare, quelli non indicati vanno a NULL
   )
SELECT 
   --campi corrispondenti alla tabella nella quale copiare
FOR cars_cont_tbp_contratti
WHERE id_contratto = 69

BULK INSERT

bulkinsert
Attenzione a che SQL SERVER abbia i diritti necessari nella cartella in cui si accede e che possa vederla.
Attenzione nel caso ci siano colonne campi con identity (es la prima colonna) occorre prevedere anche il contenuto per quel campo ovviamente lasciandolo vuoto es. di testo nel file con 3 campi di cui il primo è identity:


,primo1,secondo1
,primo2,secondo2


esempi 


BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = ',')

Altro esempio: 

bulk insert SDS_Smarten_TMP from '{0}'
		with  (FIRSTROW=2,FIELDTERMINATOR='#',ROWTERMINATOR='\\n');

Conversioni

Date

  • Da SQLUsa
  • Da Microsoft, con spiegazioni dei codici: msdn 
SELECT Convert(datetime, '10/23/2017', 101) -- mm/dd/yyyy
SELECT Convert(datetime, '23/11/2017', 103) -- dd/mm/yyyy
SELECT Convert(datetime, '23-11-2017', 105) -- dd-mm-yyyy
SELECT Convert(datetime, '2010-11-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)   !!!
SELECT Convert(datetime, '2016-11-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

Lavorare con le Geometry

Esempi sciolti

Definizione di una WKT (well known text) da una geometry (in questo caso è la definizione di un punto sul globo): 

DECLARE @p geometry;
SET @p = geometry::STGeomFromText('POINT (0 0)', 4326);
 
SELECT @p.ToString(), @p.STBuffer(1).ToString();

Mappa e Link

MS SQL | TSQL Linguaggio | Funzioni base | Script Utili | Script Utili2 | Tips

C Sharp | Tips programmazione

Retrieved from "http://wiki.aino.it/index.php?title=MSSQL:Script_Utili&oldid=8367"
Author
Giuseppe AINO