Utilizzo query in variabile

ilcress

il 26 set 2020, 13:09

5 risposte

ilcress

Pochi posts

Iscritto da: lug, 2013

Messaggi:: 6

Utilizzo query in variabile

26 set 2020, 13:01

Ciao a tutti,
periodicamente una procedura effettua dei controlli, e invia delle email da SQLServer.
Tra tutte le colonne allarmate, solo alcune contengono dati, facilitando le scuse di "not working" da parte di chi lavora, o dovrebbe lavorare il dato .

Il codice SQL che genera il codice HTML da inviare per email originale è questo, ed è funzionante alla perfezione:

		SET @tableUPDATE_ERR= 
		'<table cellspacing="0" border="1"><caption></caption><tr>
		<th>Nome prodotto</th>
		<th>controllo1</th>
		<th>controllo2</th>
		<th>controllo3</th>
		<th>controllo4</th>
		<th>controllo5</th>
		<th>controlloN</th>
		</tr>'+
		CAST(
		(
		SELECT																				
		TD="Nome prodotto",''													
		, TD=controllo1,''																		
		, TD=controllo2,''																			
		, TD=controllo3,''																			
		, TD=controllo4,''																		
		, TD=controllo5,''
		, TD=controlloN,''
		[DIVERSE ALTRE COLONNE]   
		FROM #tabella_dinamica_invio_email
		FOR XML PATH('tr'),type
		) AS VARCHAR(MAX))+'</table>';
		SET @tableUPDATE_ERR = @CSS + '<br />'+@tableUPDATE_ERR

non tutte le colonne sono popolate, quindi, tramite un cursore, elimino le colonne vuote:

		DECLARE @COLUMN_NAMEcur VARCHAR(512) ;
		DECLARE @SCRIPT         VARCHAR(512) ;
		DECLARE crs CURSOR
		FOR
		SELECT
		DISTINCT COLUMN_NAME
		FROM tempdb.INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME like '#tabella_dinamica_invio_email%'
		open crs
		FETCH NEXT FROM crs INTO @COLUMN_NAMEcur
		while @@FETCH_STATUS=0
		BEGIN

		set @script='
		if (select count(*) from #tabella_dinamica_invio_email where "' + @COLUMN_NAMEcur + '" != '''') = 0
		ALTER TABLE #tabella_dinamica_invio_email DROP column "' + @COLUMN_NAMEcur + '" ;
		'
		exec(@script)

		FETCH NEXT FROM crs INTO @COLUMN_NAMEcur
		END
		CLOSE crs
		DEALLOCATE crs

La tabella adesso ha i giusto sizing, ho solo le colonne dispare (Nome prodotto, colonna 1, colonna 3 e colonna 5).
Quando però devo rilanciare la select, passandola come variabile perchè le colonne popolate potrebbero variare, la query (bindata in @COLONNE_VALIDE2) mi viene stampata e non eseguita:

    
    DECLARE @tableUPDATE_ERR      NVARCHAR(MAX) ;
    DECLARE @text                 NVARCHAR(MAX) ;
    DECLARE @CSS                  NVARCHAR(MAX) ;
    
    SET @CSS = 
    	N'<style type="text/css">'		 +
    	N' table, p { font-size:13px;'	 +
    	N'		 font-family:Arial;}'  +
    	N' div { max-height:300px;overflow:auto; }'  +
    	N' tr th {color:#ff0000 ;}'  +
    	N'</style>';
    
    DECLARE @COLONNE_VALIDE1 nVARCHAR (MAX)
    DECLARE @COLONNE_VALIDE2 nVARCHAR (MAX)
    
    set @COLONNE_VALIDE1=(SELECT STUFF((SELECT '' + COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like '#tabella_dinamica_invio_email%' FOR XML PATH('th')), 1, 0, ''))
    set @COLONNE_VALIDE2='CAST(
    (
    SELECT ' + (SELECT STUFF((SELECT '' + ', TD="'+COLUMN_NAME +'",','''''' FROM tempdb.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME like '#tabella_dinamica_invio_email%' FOR XML PATH('')), 1, 1, ''))
    +
    '
    FROM #tabella_dinamica_invio_email
    FOR XML PATH(''tr''),type
    ) AS nVARCHAR(MAX))+''</table>''
    ' 
    SET @tableUPDATE_ERR= '<table cellspacing="0" border="1"><caption></caption><tr>'+ @COLONNE_VALIDE1 +'</tr>'+@COLONNE_VALIDE2
    
    PRINT(@tableUPDATE_ERR)

Avete qualche idea sul come possa aggirare questa situazione?

Grazie mille

5 Risposte

sspintux
Molto attivo
Iscritto da
dic, 2012
Messaggi:
501
Re: Utilizzo query in variabile
26 set 2020, 22:33
Personalmente, ad esser sincero, non ho ben capito qual'è il vero problema

ilcress

Pochi posts

Iscritto da: lug, 2013

Messaggi:: 6

Re: Utilizzo query in variabile

27 set 2020, 13:10

Come darti torto?! Effettivamente sono stato un po' laconico. Riprovo.

Devo generare una tabella HTML dinamica da SQLServer, non solo dinamica in termini di righe, ma anche di colonne.
Prova adesso questo codice:

declare @tableUPDATE_ERR varchar(max)
declare @css varchar(max)
print '--- PRIMO METODO FUNZIONANTE (TUTTE LE COLONNE)--- '
IF OBJECT_ID(N'mydb.dbo.GIOCATORI') IS NOT NULL DROP TABLE mydb.dbo.GIOCATORI
CREATE TABLE mydb.dbo.GIOCATORI (PLAYER VARCHAR(10),"2010" VARCHAR(10),"2011" VARCHAR(10),"2012" VARCHAR(10),"2013" VARCHAR(10),"2014" VARCHAR(10),"2015" VARCHAR(10),"2016" VARCHAR(10),"2017" VARCHAR(10),"2018" VARCHAR(10),"2019" VARCHAR(10),"2020" VARCHAR(10))
INSERT INTO mydb.dbo.GIOCATORI VALUES ('Rossi'   , '', '', '', '', '', '', '', '', 'X', '', 'X')
INSERT INTO mydb.dbo.GIOCATORI VALUES ('Bianchi' , '', '', '', '', '', '', '', 'X', 'X', '', '')
INSERT INTO mydb.dbo.GIOCATORI VALUES ('Verdi'   , 'X', '', '', '', '', '', '', '', '', '', '')
INSERT INTO mydb.dbo.GIOCATORI VALUES ('Gialli'  , '', '', '', '', '', '', '', '', '', '', '')
INSERT INTO mydb.dbo.GIOCATORI VALUES ('Blu'     , '', '', 'X', 'X', 'X', '', '', '', '', '', '')

SET @CSS = 
	N'<style type="text/css">'		 +
	N' table, p { font-size:13px;'	 +
	N'		 font-family:Arial;}'  +
	N' div { max-height:300px;overflow:auto; }'  +
	N' tr th {color:#ff0000 ;}'  +
	N'</style>';

SET @tableUPDATE_ERR= 
'<table cellspacing="0" border="1"><caption></caption><tr><th>PLAYER</th><th>2010</th><th>2011</th><th>2012</th><th>2013</th><th>2014</th><th>2015</th><th>2016</th><th>2017</th><th>2018</th><th>2019</th><th>2020</th></tr>'+
CAST(
(
SELECT																				
  TD="PLAYER",''
, TD="2010"  ,''							
, TD="2011"  ,''							
, TD="2012"  ,''							
, TD="2013"  ,''
, TD="2014"  ,''
, TD="2015"  ,''
, TD="2016"  ,''
, TD="2017"  ,''
, TD="2018"  ,''
, TD="2019"  ,''
, TD="2020"  ,''
FROM mydb.dbo.GIOCATORI
FOR XML PATH('tr'),type
) AS VARCHAR(MAX))+'</table>';
SET @tableUPDATE_ERR = @CSS + '<br />'+@tableUPDATE_ERR

print (@tableUPDATE_ERR)

Se incolli la print in un file HTML vedrai che ci sono delle colonne inutilizzate, vorrei rimuoverle, ma come?!
Mi sono fatto la punta al cervello e alla fine ho risolto con un replace:

declare @tableUPDATE_ERR varchar(max)
declare @css varchar(max)
print '--- QUARTO METODO --- '
IF OBJECT_ID(N'mydb.dbo.GIOCATORI4') IS NOT NULL DROP TABLE mydb.dbo.GIOCATORI4
CREATE TABLE mydb.dbo.GIOCATORI4 (PLAYER VARCHAR(10),"2010" VARCHAR(10),"2011" VARCHAR(10),"2012" VARCHAR(10),"2013" VARCHAR(10),"2014" VARCHAR(10),"2015" VARCHAR(10),"2016" VARCHAR(10),"2017" VARCHAR(10),"2018" VARCHAR(10),"2019" VARCHAR(10),"2020" VARCHAR(10))
INSERT INTO mydb.dbo.GIOCATORI4 VALUES ('Rossi'   , '', '', '', '', '', '', '', '', 'X', '', 'X')
INSERT INTO mydb.dbo.GIOCATORI4 VALUES ('Bianchi' , '', '', '', '', '', '', '', 'X', 'X', '', '')
INSERT INTO mydb.dbo.GIOCATORI4 VALUES ('Verdi'   , 'X', '', '', '', '', '', '', '', '', '', '')
INSERT INTO mydb.dbo.GIOCATORI4 VALUES ('Gialli'  , '', '', '', '', '', '', '', '', '', '', '')
INSERT INTO mydb.dbo.GIOCATORI4 VALUES ('Blu'     , '', '', 'X', 'X', 'X', '', '', '', '', '', '')

SET @CSS = 
	N'<style type="text/css">'		 +
	N' table, p { font-size:13px;'	 +
	N'		 font-family:Arial;}'  +
	N' div { max-height:300px;overflow:auto; }'  +
	N' tr th {color:#ff0000 ;}'  +
	N'</style>';


SET @tableUPDATE_ERR= '
<table cellspacing="0" border="1"><caption></caption><tr>
<th>PLAYER</th> ' +
  + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2010"!= '')>0 THEN '<th>2010</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2011"!= '')>0 THEN '<th>2011</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2012"!= '')>0 THEN '<th>2012</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2013"!= '')>0 THEN '<th>2013</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2014"!= '')>0 THEN '<th>2014</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2015"!= '')>0 THEN '<th>2015</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2016"!= '')>0 THEN '<th>2016</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2017"!= '')>0 THEN '<th>2017</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2018"!= '')>0 THEN '<th>2018</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2019"!= '')>0 THEN '<th>2019</th>' ELSE '<th>$ColumnEmpty$</th>' END   + '
' + CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2020"!= '')>0 THEN '<th>2020</th>' ELSE '<th>$ColumnEmpty$</th>' END   + 
'
</tr>'+
CAST(
(
SELECT																				
  TD=      "PLAYER"                 ,''
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2010"!= '')>0 THEN "2010" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2011"!= '')>0 THEN "2011" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2012"!= '')>0 THEN "2012" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2013"!= '')>0 THEN "2013" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2014"!= '')>0 THEN "2014" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2015"!= '')>0 THEN "2015" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2016"!= '')>0 THEN "2016" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2017"!= '')>0 THEN "2017" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2018"!= '')>0 THEN "2018" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2019"!= '')>0 THEN "2019" ELSE '$ColumnEmpty$' END,''							
, TD=      CASE WHEN (select count(*) from mydb.dbo.GIOCATORI4 where "2020"!= '')>0 THEN "2020" ELSE '$ColumnEmpty$' END,''							

FROM mydb.dbo.GIOCATORI4
FOR XML PATH('tr'),type
) AS VARCHAR(MAX))+'</table>';

SET @tableUPDATE_ERR= replace(@tableUPDATE_ERR,'<TD>$ColumnEmpty$</TD>','')
SET @tableUPDATE_ERR= replace(@tableUPDATE_ERR,'<Th>$ColumnEmpty$</Th>','')

SET @tableUPDATE_ERR = @CSS + '<br />'+@tableUPDATE_ERR

print (@tableUPDATE_ERR)

Quindi ho risolto, pensavo di giocarmela con un cursore, invece me la sono cavata con una banalissima replace.
Ti ringrazio ad ogni modo di aver trovato il tempo e di leggere e rispondere al mio post.

Se poi vogliamo confrontarci sulla soluzione (un po' cozzala), a disposizione.

sspintux
Molto attivo
Iscritto da
dic, 2012
Messaggi:
501
Re: Utilizzo query in variabile
27 set 2020, 21:22
Forse ora ho capito meglio il contesto, ovvero :
"fare tutto con sql server compresa composizione testo ed invio email";

.... allora niente da dire, solo una domanda :

ma perchè deve fare tutto SqlServer compresa composizione 'rognoso' testo dell'email che non sembra proprio il suo mestiere ?
non potrebbe semplicemente fornire dati grezzi (...ed in questo è molto bravo) ad un programma che si occupa
di trattarli ed inviare l'email ?
I
ilcress
Pochi posts
Iscritto da
lug, 2013
Messaggi:
6
Re: Utilizzo query in variabile
27 set 2020, 22:15
sspintux ha scritto:
Forse ora ho capito meglio il contesto, ovvero :
"fare tutto con sql server compresa composizione testo ed invio email";

.... allora niente da dire, solo una domanda :

ma perchè deve fare tutto SqlServer compresa composizione 'rognoso' testo dell'email che non sembra proprio il suo mestiere ?
non potrebbe semplicemente fornire dati grezzi (...ed in questo è molto bravo) ad un programma che si occupa
di trattarli ed inviare l'email ?
Tipo? Calcola che lavorando in una big corporate sono legato mani e piedi a rilasci ufficiali, anzi che non debba mettere questo codice sotto verifica...

Grazie!!

sspintux

Molto attivo

Iscritto da: dic, 2012

Messaggi:: 501

Re: Utilizzo query in variabile

28 set 2020, 11:16

ilcress ha scritto:
Tipo?

volendo potresti anche valutare una query pivot dinamica (specialmente se hai già i dati in forma normalizzata)
ed il risultato finale potrebbe essere gestito da un programma per creare e spedire la mail;

.... ad occhio e croce potresti anche usare il tuo metodo andando sempre di sp_executesql.

HTH


use tempdb
go

IF OBJECT_ID(N'dbo.GIOCATORI4') IS NOT NULL DROP TABLE dbo.GIOCATORI4
GO
IF OBJECT_ID(N'tmpdb..#t') IS NOT NULL DROP TABLE #t
GO
-- per brevità ho messo solo 3 colonne 
CREATE TABLE dbo.GIOCATORI4 (PLAYER VARCHAR(10),"2010" VARCHAR(10),"2011" VARCHAR(10),"2012" VARCHAR(10))
INSERT INTO dbo.GIOCATORI4 VALUES ('Rossi'   , '', '', '')
INSERT INTO dbo.GIOCATORI4 VALUES ('Bianchi' , '', 'X', '')
INSERT INTO dbo.GIOCATORI4 VALUES ('Verdi'   , '', 'X', '')
INSERT INTO dbo.GIOCATORI4 VALUES ('Gialli'  , '', '', '')
INSERT INTO dbo.GIOCATORI4 VALUES ('Blu'     , '', '', 'X')

-- riporto la GIOCATORI4 ad una forma normalizzata ed escludo i dati  pari a stringa a lunghezza 0
select * 
into #t
from
(
	select Player,
		   q.Anno,
			case q.anno
			 when '2010' then [2010]
			 when '2011' then [2011]
			 when '2012' then [2012]
			end as Val
	from dbo.GIOCATORI4
	cross join
	(select '2010' as Anno union all select '2011' union all select '2012') q
) q
where q.val<>''
order by q.PLAYER, q.Anno

-- questa se vuoi vedere tutti i player anche se non hanno dati in nessun anno
-- altrimenti non eseguirla
insert into #t (player,anno,val)
select p.PLAYER,t.Anno,'' as val
from
(select distinct Anno from #t) as t
cross join
(
	select distinct player from dbo.GIOCATORI4
	where player not in (select player from #t)
) p

-- costruisco una query PIVOT dinamica
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(q.Anno)
FROM 
(select distinct Anno from #t ) q
order by q.anno

SET @sql = N'
SELECT p.Player,' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT Player,Anno,Val FROM #t
) AS j
PIVOT
(
  MAX(Val) FOR Anno IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p
order by p.Player;';

-- PRINT @sql;

exec sp_executesql @sql;

Devi accedere o registrarti per scrivere nel forum

5 risposte