SELECT convert(datetime, '23-10-2025', 105) -- dd-mm-yyyy
SELECT convert(datetime, '23/10/2020', 103) -- dd/mm/yyyy
SELECT convert(datetime, '10/25/2020', 101) -- mm/dd/yyyy
SELECT convert(datetime, '23/10/22', 3) -- dd/mm/yyyy
SELECT convert(datetime, '1/25/22', 1) -- mm/dd/yy
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(datetime, '2019/05/23', 111) -- yyyy/mm/dd

SELECT {ts '2014-10-11 23:59:59.000'}

SELECT CAST(VariabileChar AS INT) FROM Tabella
-- o
SELECT CONVERT(INT, VariabileChar) FROM Tabella
--o
SELECT 
   [Speed]
  ,(CAST([Speed] AS DECIMAL(10, 0)) * 10) AS SpeedOk_Decimal
  ,CAST(CAST((CAST([Speed] AS DECIMAL(10, 1)) * 10) AS integer) AS NVARCHAR(100)) AS SpeedOk_INT_NVARCHAR
FROM Tabella

--Il problema è quella 'E' con il '-2' !!!
SELECT CAST('5.9999999999999998E-2' AS DECIMAL(7,2))

--Il tipo FLOAT è predisposto ad interpretare i numeri esponenziali '*E-n' o '*En'
SELECT CAST('5.9999999999999998E-2' AS FLOAT)

DECLARE @varbinaryField varbinary(max);
SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3;
 
SELECT CONVERT(varchar(max),@varbinaryField,2)
--Producendo:            21232F297A57A5A743894A0E4A801FC3

DECLARE @WBS	AS Varchar(20) = 'A_12.33'
 
SELECT @WBS	AS StringSource
		, CHARINDEX('_', @WBS,0) AS FoundAtPosition

DECLARE @WBS	AS Varchar(20) = 'A_12.33'
 
SELECT @WBS	AS StringSource
		, SUBSTRING(@WBS
			, CHARINDEX('_', @WBS) + 1
			, LEN(@WBS)
			) AS Desired_SubString

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

DECLARE @testo AS varchar(max) = '123456789'
SELECT SUBSTRING(@testo, 4, LEN(@testo))

DECLARE @testo AS varchar(max) = '09876543210'
SELECT RIGHT(@testo, 5) -- restituisce: 43210

SELECT REPLACE('[Nome] è ritornato. [Nome] ha aperto la porta. [Nome] si è spaventato','[Nome]','Giuseppe'); 
--Restituirà:
--Giuseppe è ritornato. Giuseppe ha aperto la porta. Giuseppe si è spaventato

Select Replace(Str(@MyInt, 4), ' ' , '0')

DECLARE @field AS varchar(3) = '3'
SELECT RIGHT('000' + ISNULL(@field, ''), 3) -- Produce '003'
 
SELECT LEFT(ISNULL(@field,'') + '000', 3) -- Produce '300'

DECLARE @target as varchar(10) = 'aa@bb!cc!!dd~ee@'
 
SELECT @target = REPLACE(@target, invalidChar, '-')
FROM (VALUES ('~'),(''''),('!'),('@'),('#')) AS T(invalidChar)
 
SELECT @target

DECLARE @ResultTxt	AS varchar(max) = ''
 
SELECT 
	@ResultTxt += CAST(X.NrDuplication as varchar) + ', "' + X.PrjectName + '"
'
FROM	(SELECT
			COUNT(*) AS NrDuplication
			, [FullName] AS PrjectName
		FROM [RS_Plan_ProjectVCVI_Test].[dbo].[Projects]	AS S
		WHERE 1=1
			AND S.[WBS] NOT LIKE '%.%'
		GROUP BY S.[FullName]) AS X
WHERE X.NrDuplication > 1
 
SELECT @ResultTxt

DECLARE @CommaString 	varchar(max) = '',
		@QrySQL 		nvarchar(max) = ''
 
SET @QrySQL = 'SELECT @Result += cast(number as varchar(10))+'',''
FROM master..spt_values
WHERE type = ''P'';
'
 
EXEC sp_executesql @QrySQL, N'@Result varchar(max) OUT', @CommaString OUT
 
SELECT @CommaString

SELECT DATEADD(day, -1, GETUTCDATE()) AS dataMenoUnGiorno, 
       GETUTCDATE() AS dataAttuale

SELECT 
     GETUTCDATE() UTC_Time, 
     GETDATE() AS Local_Time, 
     DATEDIFF(HOUR, GETUTCDATE(), GETDATE()) AS [hour of Difference]

SELECT
	   CONVERT(date, GETDATE(), 103) AS Data_CONVERSIONE_BANALE,           --2018-11-07
           CAST(CONVERT(date, GETDATE(), 103)  as datetime),                   --2018-11-07 00:00:00.000
	   CONVERT(date, CONVERT(varchar(10), GETDATE(), 103), 103) AS Data_Data_senzaOrario,		--2017-10-18
	   CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 103), 103) AS Data_Data_mezzaNotte,	--2017-10-18 00:00:00.000
	   CONVERT(varchar(10), GETDATE(), 103) AS DataTesto,					--18/10/2017
	   GETDATE() AS DataOriginaria								--2017-10-18 16:29:22.040

DECLARE @MiaData DATETIME
SET @MiaData = '2016-12-30 16:00:00.001'
--
-- Conversione a stringa
--
SELECT CONVERT(VARCHAR, @MiaData, 120)           --2016-12-30 16:00:00
SELECT LEFT(CONVERT(VARCHAR, @MiaData, 120), 10) --2016-12-30
SELECT CONVERT(VARCHAR, @MiaData, 121)           --2016-12-30 16:00:00.001
SELECT CONVERT(varchar, '2020-01-01', 106)       --dd MMM yyyy    es:  01 Jan 2020
 
SELECT CONVERT(VARCHAR, GETDATE(), 103)		-- 26/11/2022
SELECT CONVERT(VARCHAR, GETDATE(), 106)		-- 26 Nov 2022
SELECT CONVERT(VARCHAR, GETDATE(), 112)		-- 20221126
SELECT CONVERT(VARCHAR, GETDATE(), 108)		-- 16:00:00

SELECT CAST(GETDATE() AS varchar(20));

SELECT CONVERT(varchar, GETDATE(), 108);    --Restituisce es.:    15:35:27
SELECT CONVERT(varchar(5), GETDATE(), 108); --Restituisce es.:    15:35
SELECT CONVERT(varchar(2), GETDATE(), 108); --Restituisce es.:    15

SELECT DATEPART(WK, GETDATE())

SELECT @@DATEFIRST --Restituisce il numero del primo giorno della settimana
--se la lingua è inglese, @@DATEFIRST restituirà   7
 
SET DATEFIRST 1; --Per impostare il primo giorno a lunedì

--Domenica 26 aprile è in realtà della settimana 17 e non 18 come esprime la colonna W1
--La soluzione è di togliere un giorno a qualsiasi data da processare
SELECT DATEPART(WEEK, '2020-04-26') AS W1,
       DATEPART(WEEK, DATEADD(DAY, -1, '2020-04-26')) AS W2

SELECT DATEPART(ISOWK, Getdate()) AS Week_ISO_8601

SET DATEFIRST 1 -- Imposta a lunedì il 1 giorno della settimana
 
SELECT DATEPART(WEEKDAY, GETDATE()) --Visualizzerà il numero progressivo del giorno della settimana es 5=venerdì

SELECT SUM(Cost) OVER () AS Cost
     , OrderNum
FROM Orders

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
  , OrderNum
  , CustomerNo

FROM Orders

SELECT
     [ID]
    ,[Nome]
    ,[Compleanno]
    ,ROW_NUMBER() over (ORDER BY Compleanno) AS Posizione
FROM	People
ORDER BY ID

SELECT
     [ID]
    ,[Nome]
    ,[Compleanno]
    ,ROW_NUMBER() over (ORDER BY Compleanno) AS Posizione
FROM	Utenti
WHERE RowNumber BETWEEN 2 AND 3
ORDER BY ID

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

DECLARE @dateFrom	DATETIME = convert(datetime, '2014-09-12 17:30:00', 120) 
DECLARE @dateTo		DATETIME = dateadd(DAY, 4, @dateFrom);
 
WITH idealWithDateTimes AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY si.port1, si.port2, sa.shipCode 
                                  ORDER BY si.shipCode DESC, si.shipClassId DESC) AS Number,
		    si.id, 
		    si.companyCode, 
		    i.shipClassId, 
		    si.port1, si.port2,
		    sa.shipCode, 
		    si.geoPosition1, si.geoPosition2
	FROM		Segments	SA (NOLOCK)
	INNER JOIN	Segments	SI (NOLOCK) ON si.port1 = sa.port1 
                                                       AND si.port2 = sa.port2 
                                                       AND si.companyCode = sa.companyCode
	INNER JOIN	ShipNames	N  (NOLOCK) ON n.companyCode = sa.companyCode 
                                                       AND n.shipCode = sa.shipCode
	WHERE sa.[type] = 'Actual' 
			AND si.[type] = 'Ideal'
	-- Date range
	AND (sa.departureDateTimeLT1 BETWEEN @dateFrom AND @dateTo 
		OR sa.departureDateTimeLT2 BETWEEN @dateFrom AND @dateTo)
	-- Remove wrong Ideals (different ship/shipclass)
	AND (si.shipCode = sa.shipCode
			OR (si.shipCode IS NULL)
			OR (si.shipClassId = n.shipClassId)
		)
)
 
SELECT 
	i.id, i.companyCode, i.shipClassId, i.port1, i.port2, i.shipCode, 
	i.geoPosition1, i.geoPosition2
FROM idealWithDateTimes i
WHERE i.Number = 1 -- This select the first Ideal for each ambiguity case
	AND I.shipCode = 'AU'
ORDER BY companyCode, shipCode

EXECUTE(';WITH AO AS
(
   SELECT
		   ROW_NUMBER() OVER (PARTITION BY idarticolo ORDER BY idarticolo) AS Number,
 
	      idarticolo, codicearticolo, um, costoarticoloimpianto, qtaimballo
   FROM [{0}].itcr.ftGetArticoliOrdinabiliImpianto (''{1}'',''{2}'',''{2}'') 
)
SELECT 
	AO.idarticolo, AO.codicearticolo, AO.um, AO.costoarticoloimpianto, AO.qtaimballo
FROM AO
WHERE AO.Number = 1
	') AT [{3}]

SELECT NEWID()
GO
-- Genererà un campo come il seguente
14F9C229-7D23-4C43-8407-2553DFA48B36

DECLARE @ID_Provolone uniqueidentifier
SET @ID_Provolone = NEWID()

DBCC CHECKIDENT ('[Classes]', RESEED, 37)

DECLARE @tempProducts1 TABLE	
(	
    IDProduct INT,
    Testo NVARCHAR(MAX),
    ChiaveEsternaID INT
);
 
INSERT INTO @tempProducts1
SELECT 
   *
FROM Products
WHERE 1=1

	CREATE TABLE #POD 
		(	  COMPANY_CODE				NVARCHAR(3)
			, DIVISION_CODE				NVARCHAR(3)
			, SHOP_CODE					NVARCHAR(3)
			, ORDER_NUMBER				NVARCHAR(6)
			, ORDER_DATE				DATE
			, ORDER_LINENUMBER			SMALLINT
			, PURCHASE_ORDER_TYPE_CODE	NVARCHAR(3)
			, PRODUCTION_ORDER_NUMBER	NVARCHAR(6)
			, PRODUCTION_ORDER_DATE		DATE)
 
	INSERT INTO #POD
	SELECT 
			  POD.COMPANY_CODE				
			, POD.DIVISION_CODE				
			, POD.SHOP_CODE					
			, POD.ORDER_NUMBER				
			, POD.ORDER_DATE				
			, POD.ORDER_LINENUMBER			
			, POD.PURCHASE_ORDER_TYPE_CODE	
			, POD.PRODUCTION_ORDER_NUMBER	
			, POD.PRODUCTION_ORDER_DATE		
	FROM		[PO_B_PURCHASE_ORDER_DETAIL]	AS POD (NOLOCK)
	INNER JOIN	[PO_B_PURCHASE_ORDER_HEADER]	AS POH (NOLOCK)	ON POD.COMPANY_CODE		= POH.COMPANY_CODE
																AND POD.DIVISION_CODE	= POH.DIVISION_CODE
																AND POD.SHOP_CODE		= POH.SHOP_CODE
																AND POD.ORDER_NUMBER	= POH.ORDER_NUMBER
																AND POD.ORDER_DATE		= POH.ORDER_DATE
	INNER JOIN	[SY_GENERAL_STATUS]				AS GS  (NOLOCK) ON POH.COMPANY_CODE		= GS.COMPANY_CODE
																AND POH.DIVISION_CODE	= GS.DIVISION_CODE
																AND POH.ORDER_STATUS	= GS.STATUS_CODE
																AND GS.ENTITY_TYPE_CODE = 'PO'
	WHERE POD.COMPANY_CODE		= @COMPANY_CODE
		AND POD.DIVISION_CODE	= @DIVISION_CODE
		AND POD.SHOP_CODE		= @SHOP_CODE
		AND (POH.ORDER_STATUS	= @ORDER_STATUS 
				OR @ORDER_STATUS IS NULL)		
		--Only PO with Status still Active
		AND ((GS.[DT_END] IS NULL AND GETUTCDATE() >= GS.[DT_START]) 
				OR (GETUTCDATE() BETWEEN GS.[DT_START] AND GS.[DT_END])
			)	
--------------------	etc
DROP TABLE #POD

DECLARE @TABLE_NAME AS  VARCHAR(50)  = ''
 
SET @TABLE_NAME = 'tempdb..#POD'
if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
 
   and o.id = object_id(@TABLE_NAME)
)
	BEGIN
	Print @TABLE_NAME + ' exists, dropping'
	DROP TABLE #POD;
	END
ELSE
	BEGIN
	Print @TABLE_NAME + ' NOT exists'
	END

	IF (OBJECT_ID('tempdb..#TableList') IS NOT NULL)
	BEGIN
		DROP TABLE #TableList
		Print '#TableList dropped.'
	END
	ELSE
		Print 'OK #TableList doesn''t exists.'

DECLARE @LogDescription AS varchar(max);
 
SET @LogDescription = 'Inizio: ' + CONVERT(varchar, GETDATE(), 121);
RAISERROR(@LogDescription,0,1) WITH NOWAIT;
 
BEGIN  
	SET @LogDescription = 'inizio (dentro): ' + CONVERT(varchar, GETDATE(), 121);
	RAISERROR(@LogDescription,0,1) WITH NOWAIT;
 
	--!!!!!!!!!!!!!!!!!!!!!!
    WAITFOR DELAY '00:00:10';  --10 secondi di attesa
 
	SET @LogDescription = 'Fine (dentro): ' + CONVERT(varchar, GETDATE(), 121);
	RAISERROR(@LogDescription,0,1) WITH NOWAIT;
END;  
SET @LogDescription = 'Fine: ' + CONVERT(varchar, GETDATE(), 121);
RAISERROR(@LogDescription,0,1) WITH NOWAIT;

SELECT [ProjectID]
 
	,(SELECT MIN(v) 
	   FROM (VALUES ( [L0 approved]), ([G0 approved]), ([G1 approved])
					,([G2 approved]), ([G5 approved]), ([G6 approved])
					,([M1 approved]), ([M2 approved]), ([M3 approved])
			) AS VALUE(v)) AS [MinDate]
	,(SELECT MAX(v) 
	   FROM (VALUES ( [L0 approved]), ([G0 approved]), ([G1 approved])
					,([G2 approved]), ([G5 approved]), ([G6 approved])
					,([M1 approved]), ([M2 approved]), ([M3 approved])
			) AS VALUE(v)) AS [MaxDate]
 
      ,[L0 approved]
      ,[G0 approved],[G1 approved],[G2 approved],[G5 approved],[G6 approved] 
      ,[M1 approved],[M2 approved],[M3 approved]   
FROM [SRC_ProjectsDE_PPM].[dbo].[Data$]

SELECT 
   MAX(colonna)
FROM tabella
WHERE colonna < (SELECT 
                 MAX(colonna)
                 FROM tabella)

DECLARE @Dividend	AS int = 0,
		@Divisor	AS int = 3,
		@i			AS int = 1,
		@n			AS int = 20
 
Print '@Dividendo % @Divisore = @Resto'
WHILE (@i < @n)
BEGIN
	SET @Dividend = @i
	Print CAST(@Dividend AS varchar) + ' % ' + CAST(@Divisor AS varchar) + ' = ' + CAST((@Dividend % @Divisor) AS varchar)
	/*
	SELECT @Dividendo AS Dividendo 
		,  @Divisore AS Divisore
		, (@Dividendo % @Divisore) AS Resto
		*/
	SET @i = @i + 1
END

DECLARE @PathRootToCheck	VARCHAR(255) = 'D:\Documenti\DOC Files\Vodafone\_Attivita',
		@FolderToCheck		VARCHAR(255) = 'Migrazione TTU Premium PIN ver 2.0'		
DECLARE @ResultSet	TABLE (Directory varchar(200))
 
INSERT INTO @ResultSet
    EXEC master.dbo.xp_subdirs @PathRootToCheck
 
IF (EXISTS (SELECT 1
	    FROM @ResultSet 
	    WHERE Directory = @FolderToCheck))
	Print 'Found: 
	' + @PathRootToCheck + '\' + @FolderToCheck
ELSE
	Print 'NOT Found: 
	' + @PathRootToCheck + '\' + @FolderToCheck

DECLARE @PathToCheck	VARCHAR(512) = 'D:\Documenti\DOC Files\Vodafone\_Attivita\Migrazione TTU Premium PIN ver 2.0',
		@FileName		VARCHAR(256) = 'Segrate-dati_CLI_PIN.txt',
		@PathFull		VARCHAR(512),
		@ResultFExists	INT,
		@BlnFExixts		BIT
 
SET @PathFull = @PathToCheck + '\' + @FileName
EXEC master.dbo.xp_fileexist @PathFull, @ResultFExists OUTPUT
SET @BlnFExixts = CAST(@ResultFExists as bit)
 
IF @BlnFExixts = 1
	Print 'Exixts
	' + @PathFull
ELSE
	Print 'NOT FOUND
	' + @PathFull

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;

dbo.fn_FileExists(filepath)

DECLARE @PathRootToCheck	VARCHAR(255) = '"D:\Tmp\Nome con spazi"', --Notare il delimitatore "
	@DOSCmd		        VARCHAR(255) = ''
 
DECLARE    @FileList TABLE 
                     (ID       int IDENTITY, 
                      FileName varchar(100));
SET @DOSCmd = 'dir ' + @PathRootToCheck + ' /b' --Parametro /b non fa vedere altri dettagli come il sommario etc
INSERT INTO @FileList
EXEC xp_cmd shell @DOSCmd

DECLARE @PathRootToCheck	VARCHAR(255) = 'D:\Tmp\Nome con spazi' --Senza "
EXEC xp_dirtree @PathRootToCheck, 1, 1 --Il primo 1 indica il livello il secondo se directory o anche files

-- Per avere l'elenco del contenuto della cartella corrente. 
-- Risultato in griglia, una riga per ogni riga di testo in output
xp_c mdshell 'Dir'

DECLARE @result			AS int,
	@ExeConsoleApp		AS varchar(256) = 'ExcelAgentCTRL.exe',
	@PathApp		AS varchar(256) = 'D:\test\App C#',
	@StrParams		AS varchar(100) = '',
	@cmd			AS sysname = '', 
	@var			AS sysname = '';
 
SET @StrParams = '"vSBC_Traffic KPIs_W8-W9.xlsx"'
SET @cmd = 'CMD /S /C " "' + @PathApp + '\' + @ExeConsoleApp + '"'
 
IF ISNULL(@StrParams, '') <> ''
BEGIN
    SET @cmd = @cmd + ' ' + @StrParams
END
 
SET @cmd = @cmd + ' "'
Print @cmd
 
EXEC @result = xp_c mdshell @cmd; 
IF (@result = 0)  
	Print 'Success'  
ELSE  
	Print 'Failure';
Print 'End'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=============================================
Author:			Giuseppe AINO
Create date:	25/10/2021
Description:	Poiché per molte procedure DEVE essere attivo il path verso il Gateway Server Power BI
				con questa procedura si verifica che sia attivo il Drive virtuale H: e quindi il virtual path:
					\\69.69.69.69\d$\Documents pizza /USER:rut\udente /PERSISTENT:yes
				se non lo fosse si attiva previa cancellazione delle precedenti impostazioni.
=============================================*/
CREATE PROCEDURE CheckAndSet_GatewayVirtualPath
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @OS_User				AS varchar(100) = 'udente',
		@OS_UserPW				AS varchar(100) = 'pizza',
		@CmdShellDOS			AS varchar(2000) = '',
		@VirtualDriveRoot		AS nvarchar(max) = 'H:',
		@VirtualPath			AS nvarchar(max) = '\\69.69.69.69\d$\Documents §password§ /USER:rut\§userName§ /PERSISTENT:yes',
		@ShellRowOutput			AS nvarchar(max),
		@ShellReturnCode		AS int
 
	DECLARE @ShellDOSOutput TABLE (ShellRowOutput nvarchar(max));
 
	SET @VirtualPath = REPLACE(@VirtualPath, '§userName§', @OS_User)
	SET @VirtualPath = REPLACE(@VirtualPath, '§password§', @OS_UserPW)
 
	SET @CmdShellDOS = 'Dir ' + @VirtualDriveRoot
 
	INSERT INTO @ShellDOSOutput
	EXEC @ShellReturnCode = master..XP_C MDSHELL @CmdShellDOS
 
	--	@ShellReturnCode=0	=>	OK
	--	@ShellReturnCode=1	=>	ko
 
	IF @ShellReturnCode <> 0
	BEGIN
		Print '@ShellReturnCode=' + CAST(@ShellReturnCode AS varchar)
		SELECT TOP 1 
			@ShellRowOutput = ShellRowOutput
		FROM @ShellDOSOutput
		WHERE ShellRowOutput IS NOT NULL
		Print 'Errore ricevuto: "' + @ShellRowOutput + '"'
 
		IF CHARINDEX('cannot find the path', @ShellRowOutput, 0) > 0
		BEGIN
 
			Print 'Si ripristinerà il path: "' + @VirtualPath + '"'
 
			SET @CmdShellDOS = 'net use ' + @VirtualDriveRoot + ' /delete'
			Print '1/2. ' + @CmdShellDOS
			EXEC XP_C MDSHELL @CmdShellDOS
 
			SET @CmdShellDOS = 'net use ' + @VirtualDriveRoot + ' ' + @VirtualPath
			Print '2/2. ' + @CmdShellDOS
			EXEC XP_C MDSHELL @CmdShellDOS
		END
	END
	ELSE
		Print 'OK è ATTIVO il path: "' + @VirtualPath + '"'
 
END
GO

INSERT INTO ApiRequestLogs 
    (ApiKey, 
    Username, 
    IPAddress, 
    [Action], 
    QueryString, 
    Report, 
    AuthResult, 
    ExecResult,
    LogDateUTC,
    Duration)
VALUES 
    (@apiKey, 
    @username, 
    @ipAddress, 
    @action, 
    @queryString, 
    @report, 
    @authResult, 
    @execResult,
    GETUTCDATE(),
    @duration);
SELECT @@IDENTITY;

DECLARE @UltimoID TABLE (ID int);
 
INSERT INTO @UltimoID
EXEC server.master..sp_executesql N'
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY()';
 
SELECT * 
FROM @UltimoID;

SELECT *
FROM OPENQUERY(server, '
                        INSERT INTO database.schema.table (columns) VALUES (values);
                        SELECT SCOPE_IDENTITY() AS ID');

SELECT IDENT_CURRENT('NomeTabellaConID_Chiave')

UPDATE miaTabella   
   SET mioCampo = N'Polpetta'
WHERE ID = 696969
IF @@ROWCOUNT = 0
   Print 'ATTENZIONE nessun aggiornamento effettuato';

DECLARE @SQLCommand		AS NVARCHAR(MAX),
	@TableName		AS NVARCHAR(50) = '[CLI_PIN]'
 
SET @SQLCommand = 'SELECT * 
FROM [MVNOTeleTu].dbo.' + @TableName
 
EXECUTE sp_executesql @SQLCommand

DECLARE @SqlQuery	NVARCHAR(MAX),
	@NomeTabella	varchar(100) = '[MiaTabella]'
 
SET @SqlQuery = 'SELECT TOP (1) @Id = ([ID]) 
				 FROM ' + @NomeTabella + '
				 WHERE [ModulId] = @IdModul';
 
EXEC SP_EXECUTESQL @SqlQuery, N'@Id INT OUTPUT, @IdModul INT',
			  @IdModul = @IdModul, @Id = @Id OUTPUT

   Declare @Nr AS Int = 123
   Print 'Testo: ' + CAST(ISNULL(@Nr, -1) AS VARCHAR(10)) + ' Records.' 
         + CHAR(10) + CHAR(13) + 'Nuova linea accapo'

--Per visualizzare i precedenti Print eventualmente sospesi
RAISERROR( '...',0,1) WITH NOWAIT

SET NOCOUNT ON;   --Disabilita l'output ed il conteggio
--Loop con ad es. inserimenti
SET NOCOUNT OFF;   --Riabilita l'output del messaggio ed il conteggio

--Esempio inserendo 5 seconti
SELECT GETDATE() OraAttuale
WAITFOR DELAY '00:00:05' -- Ritardo di 5"
SELECT GETDATE() OraAttuale

DECLARE @FormattedDelayValue	AS varchar(10) = '00:00:03'
 
SELECT GETDATE() CurrentTime;
 
WAITFOR DELAY @FormattedDelayValue
 
SELECT GETDATE() CurrentTime;

DECLARE @TABLE_NAME	AS	VARCHAR(50)  = 'PO_B_ORDER_TYPE_EXT_NZL'
IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = @TABLE_NAME))
	BEGIN
        Print 'Table ' + @TABLE_NAME + ' already created'
	END
ELSE
	BEGIN
        Print 'Table ' + @TABLE_NAME + ' will be created'	
-------------------------------------------------------------------------
	CREATE TABLE [dbo].[PO_B_ORDER_TYPE_EXT_NZL](
		[COMPANY_CODE] [nvarchar](3) NOT NULL,
		[DIVISION_CODE] [nvarchar](3) NOT NULL,
		[ORDER_TYPE_CODE] [nvarchar](3) NOT NULL,
		[ORDER_TYPE_DESCR] [nvarchar](255) NULL,
		[DT_START] [date] NULL,
		[DT_END] [date] NULL,
		[DT_INSERT] [datetime] NOT NULL,
		[USERINSERT] [nvarchar](50) NOT NULL,
		[DT_UPDATE] [datetime] NOT NULL,
		[USERUPDATE] [nvarchar](50) NOT NULL,
		[ROWGUID] [uniqueidentifier] NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[COMPANY_CODE] ASC,
		[ORDER_TYPE_CODE] ASC,
		[DIVISION_CODE] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
-------------------------------------------------------------------------
	END

USE AdventureWorks2008;
 
IF OBJECT_ID ('Contacts1', 'U') IS NOT NULL
DROP TABLE dbo.Contacts1;
 
SELECT *
INTO dbo.Contacts1
FROM AdventureWorks2008.Person.Person
WHERE 1 = 2;

IF (EXISTS (SELECT 1  -- 1 sta per il primo campo della tabella, modo efficiente ed effice
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = 'dbo'          -- DB schema
                 AND  TABLE_NAME = 'MiaTabella'))--Tabella da verificare esistenza
BEGIN
    --Fai cose
END

DECLARE @TableName As varchar(max) = 'MiaTabellaDaCercare'
 
IF EXISTS (SELECT * FROM sys.tables 
	   WHERE [name] = @TableName )
BEGIN
     SELECT 1
END
ELSE
BEGIN
     SELECT 0
END

CREATE TABLE #TableList (
				Id_Progr		INT IDENTITY
				)
---------------------
IF (OBJECT_ID('tempdb..#TableList') IS NOT NULL)
BEGIN
	DROP TABLE #TableList
	Print 'Table dropped.'
END
ELSE
	Print 'OK Table doesn''t exists.'

CREATE PROCEDURE SP_Principale
	@id_in INT
AS
BEGIN
    DECLARE @id_out INT
    EXEC SP_Chiamata @id_in, @id_out OUTPUT
    SELECT @hid_out
END
GO
------------------------------
CREATE PROCEDURE SP_Chiamata
	@id INT, 
	@id_out INT OUTPUT
AS 
BEGIN
    IF @id = 1
        SET @id_out = 100
    ELSE
        SET @id_out = 200
END
GO

DECLARE @DGT_TestDate		DateTime,
		@DGT_Message	varchar(255),
		@i		int = 0,
		@NrCall		int = 5
WHILE @i < 10
BEGIN
	SET @DGT_TestDate = GETDATE()
	Print CAST(@i AS varchar) + ' iterazione'
 
	SET @DGT_Message = CONVERT(varchar(12), GETDATE(), 8) + ': ' 
					+ CONVERT(varchar(11), @NrCall) 
					+ ' calls in progress, pausing...'
	RAISERROR (@DGT_Message,0,1) WITH NOWAIT
 
	WAITFOR DELAY '00:00:10' --10 secondi
 
	SET @i = @i + 1;
END

DECLARE @ROOM_CODE			AS nvarchar(10),
		@ErrorMsg			AS VARCHAR(MAX) = '',
		@ExtraErrorMsg		AS VARCHAR(MAX) = ''
 
IF @ROOM_CODE IS NULL
BEGIN	
	SET @ErrorMsg = 'Room code not available.' + @ExtraErrorMsg
	RAISERROR(@ErrorMsg, 
				16, --Severity	16
				1)	--State		1
	Print 'Genererà un errore che verrà intercettato dalla applicazione'
END
 
Print 'Codice eseguito comunque !!!'

DECLARE @ROOM_CODE			AS nvarchar(10),
		@ErrorMsg			AS VARCHAR(MAX) = '',
		@ExtraErrorMsg		AS VARCHAR(MAX) = ''
 
IF @ROOM_CODE IS NULL
BEGIN	
	SET @ErrorMsg = 'Room code not available.' + @ExtraErrorMsg
	RAISERROR(@ErrorMsg, 
				16, --Severity	16
				1)	--State		1
	GOTO sp_end
END
 
Print 'Esecuzione senza errori'
 
sp_end:
Print 'Fine'

THROW 51000, 'The record does not exist.', 1;

WITH CTE1 AS (SELECT 1 AS Col1),
     CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
 
SELECT 
    CTE1.Col1,CTE2.Col2
FROM       CTE1
CROSS JOIN CTE2
GO

DECLARE @StartDate		AS Date = '2022-08-08',
	@EndDate		AS Date = GetDate()
 
;WITH cte AS (SELECT 
			@StartDate AS myDate
		UNION ALL
	SELECT 
			DATEADD(day,1,myDate)
	FROM cte
	WHERE DATEADD(day,1,myDate) <=  @EndDate
)
------------- Verifica delrisultato:
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)

SELECT * 
FROM Tabella
WHERE CampoConIndice LIKE 'radice%'

--Nome della macchina che ospita il Server
SELECT host()

--DB corrente
SELECT db_name()
--StoredProcedure corrente
SELECT OBJECT_NAME(@@PROCID)

SELECT @@version
 
/* Es di Output:
 
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)   Oct 20 2015 15:36:27   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (Intel X86)   Jul  3 2017 02:37:05   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.1 <X64> (Build 7601: ) (WOW64) 
Microsoft SQL Server 2017 (RTM-GDR) (KB4494351) - 14.0.2014.14 (X64)   Apr  5 2019 09:18:51   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17134: ) (Hypervisor) 
*/
 
SELECT SERVERPROPERTY('Edition')
 
/* Output:
Developer Edition (64-bit)
*/
 
SELECT SERVERPROPERTY('LicenseType')
     , SERVERPROPERTY('NumLicenses')
     , SERVERPROPERTY('productversion')
     , SERVERPROPERTY ('productlevel') 
     , SERVERPROPERTY ('edition')

sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
EXEC sp_configure 'show advanced option', '1'; 
 
RECONFIGURE WITH OVERRIDE
GO
 
-- Correggere sottovedi commento sotto (*) !!!!
sp_configure 'xp_cmds hell', 1
 
GO
RECONFIGURE WITH OVERRIDE
GO

Use master
EXEC sp_configure 'show advanced options', 1 
RECONFIGURE; 
EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;

Use master
CREATE ASSEMBLY TextFunctions  
FROM 'D:\Librerie\SHA_2\TextFunctions.dll'  
WITH PERMISSION_SET = SAFE;

SELECT 
  * 
FROM sys.assembly_files

USE [master]
GO
 
CREATE PROCEDURE [dbo].[xp_hash_sha]
@text [varbinary](8000),
@hash [varbinary](64) OUTPUT,
@bits [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TextFunctions].[Libreria].[GetSHA2Hash] --è il namespace delle funzioni nella DLL
GO

sp_configure 
 
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CREATE  PROCEDURE [dbo].[xsp_hash_sha]
 
	@plain_text		VARCHAR(4000),
	@bits			INT,
	@hashed_text	VARCHAR(64) OUT
 
AS
BEGIN
	SET NOCOUNT ON
	SET ROWCOUNT 0
 
	DECLARE @plain_text_binary	VARBINARY(8000)
	DECLARE @hashed_binary		VARBINARY(64)
 
	SET @hashed_text = NULL
 
	-- Check inputs
	SET @bits		= COALESCE(@bits, 0)
 
	IF @bits NOT IN (256, 384, 512)
	BEGIN
		RAISERROR('@bits must be 256, 384 or 512', 11, 1)
		GOTO exit_point
	END
 
	IF @plain_text IS NULL
	BEGIN
		GOTO exit_point
	END
 
 
	-- Convert the plaintext to binary and hash it
	SET @plain_text_binary = CONVERT(VARBINARY(8000), @plain_text)
	EXEC master.dbo.xp_hash_sha @plain_text_binary, @hashed_binary OUTPUT, @bits
 
	-- Convert hashed binary to text and strip hex prefix
	IF @hashed_binary IS NOT NULL
	BEGIN
		DECLARE @temp_hashed_text VARCHAR(66)
 
		SET @temp_hashed_text = UPPER(master.dbo.fn_varbintohexstr(@hashed_binary))
 
		IF LEN(@temp_hashed_text) = 66 AND LEFT(@temp_hashed_text, 2) = '0x'
		BEGIN
			SET @temp_hashed_text = RIGHT(@temp_hashed_text, 64)
		END
 
		SET @hashed_text = @temp_hashed_text
	END
 
exit_point:
 
END

DECLARE	@return_value int,
		@hashed_text varchar(64)
 
EXEC	@return_value = [dbo].[xsp_hash_sha]
		@plain_text = N'testo prova',
		@bits = 256,
		@hashed_text = @hashed_text OUTPUT
 
SELECT	@hashed_text as N'@hashed_text'

SELECT AK.name,
	AK.principal_id,
	AK.asymmetric_key_id,
	AK.pvt_key_encryption_type,
	AK.pvt_key_encryption_type_desc,
	AK.thumbprint,
	AK.algorithm,
	AK.algorithm_desc,
	AK.key_length,
	AK.sid,
	AK.string_sid,
	AK.public_key,
	AK.attested_by,
	AK.provider_type,
	AK.cryptographic_provider_guid,
	AK.cryptographic_provider_algid
FROM sys.asymmetric_keys AS AK;

DROP ASYMMETRIC KEY CLRAssembly4SQLServer_Test1

CREATE ASYMMETRIC KEY CLRAssembly4SQLServer_Test1_Key
FROM EXECUTABLE FILE = 'D:\Tmp\CLRAssembly_Test\CLRAssembly4SQLServer_Test1.dll';

USE msdb ;  
GO  
 
EXEC dbo.sp_help_jobhistory   
    @job_name = N'FoxNewZealand.AutomaticBankDepositAndCashClosure_SIT' ;  
GO

SELECT C.*
FROM		[MVNOTeleTu_Migrated_PremNr].[dbo].[T_D_CLI_PIN]	AS C
INNER JOIN	[VFT2Premiumsrv].[dbo].[CLI_PIN]			AS P ON C.CLI = P.CLI collate SQL_Latin1_General_CP1_CI_AS

USE master;
 
ALTER DATABASE YourDatabase 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
ALTER DATABASE YourDatabase 
SET MULTI_USER;

DECLARE @SQLQuery	AS NVARCHAR(max),
	@DBName		AS NVARCHAR(255) = '[RS_SRC_Avaya_AACC]'
 
SET @SQLQuery = '
USE master; 
ALTER DATABASE ' + @DBName + '
SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE  ' + @DBName + '
SET MULTI_USER;'
 
EXEC (@SQLQuery)

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO