Login Login
MORE

WIDGETS

Widgets

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

MSSQL:TSQL Linguaggio

From Aino Wiki

Jump to: navigation, search

Selezioni

Usate per acquisire dati da tabelle o per produrre l'output di funzioni, funzionalità deputata all'istruzione SELECT

Varie

Acquisizione da tabella usando numeri al posto dei nomi delle colonne di una tabella 

SELECT 
   campo1, campo2, campo3
FROM miaTabella;
 
--produrrà lo stesso risultato della seguente:
SELECT 
   1, 2, 3
FROM miaTabella;
--Utile nelle query dinamiche quando non si conosce il nome dei campi o semplicemente per comodità

Alias sul nome dei campi 

SELECT 
   campo1 AS uno, campo2 AS due, campo3 AS tre
FROM miaTabella;
--Oppure
SELECT 'Nome colonna' = 1234
--Visualizzerà il valore 1234 con intestazione di colonna "Nome colonna"


Selezione per visualizzare una LISTA\tabella 

SELECT 
	invalidChar
FROM (VALUES ('~'),(''''),('!'),('@'),('#')) AS T(invalidChar)

Tipi di dato

Link interno: Tipi di dato

Controllo del flusso

CASE WHEN

SELECT 
   CASE 
      WHEN MIN(valore) <= 0 THEN 0 
      WHEN MAX(1/valore) >= 100 THEN 1 
      ELSE
        -1
   END 
FROM Tabella

Caso in cui una azione è su più condizioni in OR: 

CASE WHEN @P1 in ('a', 'd', 'z') THEN 
        1
    WHEN @P1 in ('b', 't') THEN
        2
    ELSE 
       0 
END

Controllo di un campo NULL 

CASE WHEN @P1 IS NULL THEN 
        1
    ELSE 
       0 
END


Altro 

DECLARE @ipotesiDurataSegmento AS INT = 100 -- in minuti
 
SELECT 
	CB.[uid],CB.[CompanyCode],CB.[ShipCode],CB.[AlertId],CB.[Segment1Id],CB.[Port1],CB.[Port2],CB.[on_UTCDate],CB.[off_UTCDate]
	,DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) AS X 
 
	, DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento)
		 * 100 AS X1
 
	, CASE 
		WHEN CB.severity_type = 1 THEN
			DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento)
				* 100
		WHEN CB.severity_type = 2 THEN
			(DATEDIFF(MINUTE, CB.[on_UTCDate],CB.[off_UTCDate]) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento)
				* 100)   50
	  ELSE
		-1
	  END	
	 AS X2
 
	,CB.[severity],CB.[severity_type]
	,'|' AS I
	,A.[Id],A.[CompanyCode],A.[ShipCode],A.[ThresholdId],A.[Type],A.[TriggerDateUTC],A.[Segment1Id]
FROM		[Fact_CorridorBreach]	AS CB	(NOLOCK)
INNER JOIN	[Src_Alerts]			AS A	(NOLOCK)	ON CB.[Segment1Id] = A.[Segment1Id]
--INNER JOIN	[Dim_Segments]			AS S	(NOLOCK)	ON CB.[Segment1Id] = S.Id
--WHERE A.[ThresholdId] <> -1
--ORDER BY CB.[on_UTCDate] DESC
ORDER BY CB.Segment1Id

Goto

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'

Cicli

DO WHILE loop

WHILE (1=1)
  BEGIN
  -- Do stuff...
  IF (some_condition is true)
     BREAK;
  END

Cursore

Esiste un altro modo più efficiente di ciclare su un dataset, guida interna Cursore Emulato
Sostanzialmente i cursori sono deprecati in quanto consumano memoria e creano dei locks. Quando si apre un cursore si caricano le sue righe in memoria e si bloccano, dopo, ciclando sul cursore si fanno cambiamenti in altre tabelle che ancora si mantengono in memoria finché il cursore è aperto.
Sintassi: 

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]   
     [ FORWARD_ONLY | SCROLL ]   
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
     [ TYPE_WARNING ]   
     FOR select_statement   
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

Esempio semplice (da docs.microsoft.com): 

DECLARE vend_cursor CURSOR  
    FOR SELECT * FROM Purchasing.Vendor  
OPEN vend_cursor  
FETCH NEXT FROM vend_cursor;

Più realistico 

SET NOCOUNT ON;  
 
DECLARE @vendor_id int, @vendor_name nvarchar(50),  
    @message varchar(80), @product nvarchar(50);  
 
PRINT '-------- Vendor Products Report --------';  
 
DECLARE vendor_cursor CURSOR FOR   
SELECT VendorID, Name  
FROM Purchasing.Vendor  
WHERE PreferredVendorStatus = 1  
ORDER BY VendorID;  
 
OPEN vendor_cursor  
 
FETCH NEXT FROM vendor_cursor   
INTO @vendor_id, @vendor_name  
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ' '  
    SELECT @message = '----- Products From Vendor: '     
        @vendor_name  
 
    PRINT @message  
 
    -- Declare an inner cursor based     
    -- on vendor_id from the outer cursor.  
 
    DECLARE product_cursor CURSOR FOR   
    SELECT v.Name  
    FROM Purchasing.ProductVendor pv, Production.Product v  
    WHERE pv.ProductID = v.ProductID AND  
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor  
 
    OPEN product_cursor  
    FETCH NEXT FROM product_cursor INTO @product  
 
    IF @@FETCH_STATUS <> 0   
        PRINT '         <<None>>'       
 
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
 
        SELECT @message = '         '   @product  
        PRINT @message  
        FETCH NEXT FROM product_cursor INTO @product  
        END  
 
    CLOSE product_cursor  
    DEALLOCATE product_cursor  
        -- Get the next vendor.  
    FETCH NEXT FROM vendor_cursor   
    INTO @vendor_id, @vendor_name  
END   
CLOSE vendor_cursor;  
DEALLOCATE vendor_cursor;

Programmazione

Creazione Funzioni

Scalari

NOTA una funzione scalare può chiamare a sua volta una funzione scalare

Esempio semplice: 

CREATE FUNCTION [dbo].[RS_CastCustomDatetimeToDateTime]
(
	@CustomDateTime	AS varchar(128)
)
RETURNS Datetime --OUTPUT
AS
BEGIN
	DECLARE @ResultStandardDatetime AS Datetime,
		@PosStart				AS Smallint,
		@Lenght					AS Smallint
	/* Esempi di data di input "strampalata":
	Tipo 1
		29 March 2019 18:00
		22 February 2019 18:00
		31 October 2019 18:00
		--SELECT convert(datetime, '15 February 2019 18:00', 106)
	Tipo 2
		Fri 22/02/19
		Mon 25/02/19
		Thu 31/10/19
	*/
	IF @CustomDateTime IS NOT NULL
	BEGIN
		--Data del Tipo 2
		--		Fri 22/02/19
		IF CHARINDEX('/', @CustomDateTime, 0) > 0
		BEGIN
			SET @Lenght = LEN(@CustomDateTime)
			SET @PosStart = CHARINDEX(' ', @CustomDateTime, 0)   1 -- 5
			SET @CustomDateTime = SUBSTRING(@CustomDateTime, @PosStart, @Lenght)
			--Non è importante se l'anno è di 2 o quattro cifre "dovrebbe" funzionare ugualmente			
			IF LEN(@CustomDateTime) = 8
				SET @ResultStandardDatetime = CONVERT(datetime, @CustomDateTime, 3)
			ELSE
			IF LEN(@CustomDateTime) = 10
				SET @ResultStandardDatetime = CONVERT(datetime, @CustomDateTime, 103)
		END
		ELSE
		IF @CustomDateTime <> 'NA'
		BEGIN
			--Data del Tipo 1
			--		29 March 2019 18:00
			SET @ResultStandardDatetime = CONVERT(datetime, @CustomDateTime, 106)
		END
	END
 
	RETURN @ResultStandardDatetime
END
 
GO

ATTENZIONE non è possibile creare funzioni con più di un parametro di output, creare una funzione Tabellare piuttosto.

Tabellari

Da sqlservertutorial

Esempio semplice

CREATE FUNCTION udfProductInYear (
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

Uso: 

SELECT 
    * 
FROM 
    udfProductInYear(2017);

Esempio complesso

Data una stringa in input "Aino[30%]" restituisce una tabella di UN solo rigo con "Aino" e 30,00 

CREATE FUNCTION GetResourceNameAndPercEffort (
    @Resource_Name		AS varchar(255)		--Es.: 'Roccella[30%]'
)
RETURNS @tb_out TABLE 
					(ResourceName	varchar(255)
					, PercEffort	decimal(5, 2)
					, ErrorMessage	varchar(max))
AS
BEGIN
	DECLARE @StrPercCut			AS varchar(5) = '',
			@FromSquareBraket	AS int = 0,
			@ToSquareBraket		AS int = 0,
			------------------------------------
			@PercOut			AS Decimal(5, 2),
			@Resource_NamesOut	AS varchar(255),
			@ErrorMessage		AS varchar(max)
 
	SET @FromSquareBraket = CHARINDEX('[', @Resource_Name, 0)
 
	IF @FromSquareBraket > 0
	BEGIN
		SET @ToSquareBraket = CHARINDEX(']', @Resource_Name, @FromSquareBraket)
		IF @ToSquareBraket > 0
		BEGIN
			SET @StrPercCut = SUBSTRING(@Resource_Name, @FromSquareBraket, @ToSquareBraket)
			SET @PercOut = CAST(
								REPLACE(REPLACE(REPLACE(@StrPercCut, '%', ''), '[', ''), ']', '') 
								AS Decimal(5, 2))
			SET @Resource_NamesOut = REPLACE(@Resource_Name, @StrPercCut, '')
			SET @Resource_NamesOut = LTRIM(RTRIM(@Resource_NamesOut))
		END
		ELSE
		BEGIN
			SET @ErrorMessage  = 'Percentuale di Effort non indicata correttamente.'
		END
	END
	ELSE IF CHARINDEX('(', @Resource_Name) > 0
	BEGIN
		SET @ErrorMessage  = 'Carattere irregolare imprevisto'
	END
	ELSE
	BEGIN
		--Non trovato!
		SET @Resource_NamesOut = @Resource_Name
		SET @PercOut = -100
	END
 
 
	INSERT INTO @tb_out
		SELECT @Resource_NamesOut, @PercOut, @ErrorMessage
 
	RETURN;
END

Uso: 

DECLARE @ResourceName	AS varchar(max),
	    @PercEffort		AS decimal(5, 2),
		@ErrorMessage	AS varchar(max)
 
SELECT TOP 1
	@ResourceName = ResourceName
	, @PercEffort = PercEffort
	, @ErrorMessage = ErrorMessage
FROM dbo.GetResourceNameAndPercEffort('Aino[40%')
 
Print '@ResourceName = "'   ISNULL(@ResourceName, 'NULL') 
		  '", @PercEffort = '   CAST(ISNULL(@PercEffort, -1) AS varchar)
		  '", @ErrorMessage = '   ISNULL(@ErrorMessage, 'NULL')

Output nel secondo casp: 

@ResourceName = "Aino", @PercEffort = 40.00", @ErrorMessage = NULL

Implementazione della Split(stringaCSV)

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

Uso: 

DECLARE @txtSQL_View as VARCHAR(MAX) = '' --??
 
SELECT
	(CASE WHEN (CHARINDEX(' AS ', Value) > 0) THEN
		SUBSTRING(Value, 0, CHARINDEX(' AS ', Value) )
	ELSE
		Value
	END) AS Campo
FROM dbo.Split(@txtSQL_View, ',')

Creazione Stored Procedure

Esempio con parametro di Output 

ALTER  PROCEDURE [dbo].[webVoucherRecharge]
	@account 		VARCHAR(20),
	@pin 			VARCHAR(20),
	@voucher_id 		INT 			= NULL, 		-- Optional serial no. of the card being used for the top up
	@voucher_value		FLOAT			= NULL	OUTPUT,
	@voucher_currency	VARCHAR(10)		= NULL	OUTPUT, -- Voucher currency
	@result_status		INT				= NULL	OUTPUT,
	@result_message		VARCHAR(255)	= NULL	OUTPUT,
	@show_output		BIT 			= 1	 			-- Flag used to determine if we're to return the status resultset
AS
BEGIN
-- etc ...
END
 
------------- Esempio di esecuzione senza valorizzare i parametri:
 
DECLARE	@return_value int,
		@voucher_value float,
		@voucher_currency varchar(10),
		@result_status int,
		@result_message varchar(255)
 
EXEC	@return_value = [dbo].[webVoucherRecharge]
		@voucher_value = @voucher_value OUTPUT,
		@voucher_currency = @voucher_currency OUTPUT,
		@result_status = @result_status OUTPUT,
		@result_message = @result_message OUTPUT
 
SELECT	@voucher_value as N'@voucher_value',
		@voucher_currency as N'@voucher_currency',
		@result_status as N'@result_status',
		@result_message as N'@result_message'
 
SELECT	'Return Value' = @return_value

Creazione Viste

Nelle viste non è possibile definire un ordinamento 

CREATE VIEW [dbo].[v_Projects]
AS
	SELECT
		X.IDProject
		, X.IDOwner
		, X.FullName
		, X.[Description]
		, X.WBS
		, ISNULL(X.DateFrom, X.A_DateFrom) AS [DateFrom]
		, ISNULL(X.DateTo, X.A_DateTo) AS [DateTo]
		, (DATEDIFF(DAY, ISNULL(X.DateFrom, X.A_DateFrom), ISNULL(X.DateTo, X.A_DateTo))   1) AS DaysEffort
		, ISNULL(X.[ProgressPercentage], X.A_ProgressPercentage) AS ProgressPerc
		, X.IDStatus
		, (CASE WHEN X.[Effort] = 0 THEN 
				ISNULL(X.[A_Effort], 0)
			ELSE 
				X.[Effort]
			END) AS [Effort]
	FROM Tabela AS X
 
GO

Leggibilità

Region

Per migliorare la lettura, è possibile raggruppare zone di codice per omogeneità di funzione svolta.
Si può raggruppare tale regione del codice tra BEGIN ... END 

BEGIN   /*  Testo che spiega il contenuto della regione BEGIN .. END */
    --Codice che serve ad uno scopo omogeneo alla regione
END

Controllo dell'Errore

Dare una occhiata al link interno: Controllo errore

TRY CATCH

In questo esempio realistico si fa anche uso della Transazione: 

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRANSACTION;
 
	--ToDo
	EXEC CiccioCappuccio -- Simulo un errore, la SP non esiste
 
	------------------------------- FINE Codice in transazione
	COMMIT TRANSACTION;
	Print 'End COMMIT'
END TRY
BEGIN CATCH
	BEGIN	/*	Manage exception */
	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;
 
	DECLARE	@ERROR_SEVERITY INT,
			@ERROR_STATE    INT,
			@ERROR_NUMBER   INT,
			@ERROR_LINE     INT,
			@ERROR_MESSAGE  NVARCHAR(MAX);
 
	SELECT
		@ERROR_SEVERITY = ERROR_SEVERITY(),
		@ERROR_STATE    = ERROR_STATE(),
		@ERROR_NUMBER   = ERROR_NUMBER(),
		@ERROR_LINE     = ERROR_LINE(),
		@ERROR_MESSAGE  = ERROR_MESSAGE();
 
	----Stampa dell'errore:
	Print @ERROR_MESSAGE
 
	--La seguente blocca l'esecuzione segnalando COMUNQUE l'errore
	RAISERROR('Msg %d, Line %d, :%s',
				@ERROR_SEVERITY,
				@ERROR_STATE,
				@ERROR_NUMBER,
				@ERROR_LINE,
				@ERROR_MESSAGE);
	END
END CATCH

NOTA PURTROPPO non si può usare in una funzione !!!

Mappa e Link

MS SQL | Comandi di Query | Funzioni base | Script Utili | Script Utili2

C# | Visual Studio | PowerBI | PowerBI Programmazione

Parole chiave:

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