none
Stored Procedure ---> CTE ---> Nomi di Campo come parametri RRS feed

  • Domanda

  • Devo fare una Stored complessa (almeno per me)

    sono coinvolte una decina di tabelle o viste  quindi uso le CTE  

    Ci sono diversi parametri di valori su cui filtrare e fino a qui è tutto Ok

    Ho invece un'ultimo parametro che è il nome di un campo su cui devo applicare il filtro e qui non riesco a uscirne .... :( ....  con le mie povere forze e capacita ......

    Sotto posto una demo minimale con una tabella,  pochi record e la stored semplificata in modo da evidenziare il problema

    Grazie a chi potra aiutarmi

    <code>

    USE [Test01]
    GO

    /****** Object:  StoredProcedure [dbo].[Test]    Script Date: 02/07/2021 13:41:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[Test]
    @NomCamScelta varchar(10)
    --  Devo il nome del campo da filtrare.    Scelta1  oppure  Scelta2

    AS
    BEGIN
    SET NOCOUNT ON;

    WITH cte01 AS ( 
    -- SELECT Marca, Valore FROM dbo.Tab1 WHERE (Scelta1 = 4)
    SELECT Marca, Valore FROM dbo.Tab1 WHERE (@NomCamScelta = 4)
    -- Qui si "incricca" 
    -- Perche il parametro non è un valore bensi il nome del campo da utilizzare
    -- e non so come risolverlo
    ),

    cte02 AS (
    SELECT Marca, SUM(Valore) AS Totale FROM cte01 GROUP BY Marca
    )

    SELECT Marca, Totale FROM cte02

    END

    GO



    /****** Object:  Table [dbo].[Tab1]    Script Date: 02/07/2021 13:41:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Tab1](
    [Idxx] [int] IDENTITY(1,1) NOT NULL,
    [Marca] [nvarchar](50) NULL,
    [Valore] [int] NULL,
    [Scelta1] [int] NULL,
    [Scelta2] [int] NULL,
     CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED 
    (
    [Idxx] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET IDENTITY_INSERT [dbo].[Tab1] ON 

    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (1, N'Fiat', 8, 8, 7)
    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (2, N'Fiat', 5, 4, 5)
    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (3, N'Ford', 4, 3, 4)
    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (4, N'Audi', 7, 4, 9)
    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (5, N'Audi', 9, 4, 4)
    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (6, N'Ford', 3, 8, 2)
    GO
    INSERT [dbo].[Tab1] ([Idxx], [Marca], [Valore], [Scelta1], [Scelta2]) VALUES (7, N'Ford', 2, 7, 4)
    GO
    SET IDENTITY_INSERT [dbo].[Tab1] OFF
    GO
    </code>



    • Modificato Mancini, venerdì 2 luglio 2021 12:05
    venerdì 2 luglio 2021 12:00

Risposte

  • Ciao,

    una possibile soluzione è quella di utilizzare Dynamic SQL in questo modo:

    CREATE OR ALTER PROCEDURE [dbo].[Test]
      @NomCamScelta VARCHAR(10) = 'Scelta1'
    AS
    BEGIN
      SET NOCOUNT ON;
    
      -- Controllo parametri
      IF ((TRIM(@NomCamScelta) <> 'Scelta1')
          AND (TRIM(@NomCamScelta) <> 'Scelta2'))
        THROW 51000, 'Valore del parametro @NomCamScelta non ammesso. I valori ammessi sono Scelta1 e Scelta2!', 1;
    
      DECLARE
        @SQLCommand VARCHAR(MAX) = ''
    
      SET @SQLCommand =
      'WITH cte01 AS ' +
      '( ' +
        -- SELECT Marca, Valore FROM dbo.Tab1 WHERE (Scelta1 = 4)
        'SELECT ' +
          'Marca ' +
          ',Valore ' +
        'FROM ' +
          'dbo.Tab1 ' +
        'WHERE ' +
          '(' + @NomCamScelta + '= 4) ' +
      '), ' +
      'cte02 AS ' +
      '( ' +
        'SELECT ' +
          'Marca ' +
          ',SUM(Valore) AS Totale ' +
        'FROM ' +
          'cte01 ' +
        'GROUP BY ' +
          'Marca ' +
      ') ' +
      'SELECT ' +
        'Marca ' +
        ',Totale ' +
      'FROM ' +
        'cte02';
    
      EXEC(@SQLCommand);
    END
    GO

    Se la query diventerà complessa il debug non sarà semplice, è la soluzione "veloce" anche se non sono un fan di Dynamic SQL.

    Un'alternativa è quella di utilizzare un parametro intero che funge "switch" in funzione del quale eseguire una query piuttosto che l'altra con il nome della colonna in chiaro. La stored procedure diventerà molto "verbosa" ma sarà più chiara.. se il numero di colonne da condizionare aumenta, aumenterà di molto la "verbosità" della query.

    Fammi sapere quale soluzione adotterai :)

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn




    sabato 3 luglio 2021 21:11
    Moderatore

Tutte le risposte

  • Ciao,

    una possibile soluzione è quella di utilizzare Dynamic SQL in questo modo:

    CREATE OR ALTER PROCEDURE [dbo].[Test]
      @NomCamScelta VARCHAR(10) = 'Scelta1'
    AS
    BEGIN
      SET NOCOUNT ON;
    
      -- Controllo parametri
      IF ((TRIM(@NomCamScelta) <> 'Scelta1')
          AND (TRIM(@NomCamScelta) <> 'Scelta2'))
        THROW 51000, 'Valore del parametro @NomCamScelta non ammesso. I valori ammessi sono Scelta1 e Scelta2!', 1;
    
      DECLARE
        @SQLCommand VARCHAR(MAX) = ''
    
      SET @SQLCommand =
      'WITH cte01 AS ' +
      '( ' +
        -- SELECT Marca, Valore FROM dbo.Tab1 WHERE (Scelta1 = 4)
        'SELECT ' +
          'Marca ' +
          ',Valore ' +
        'FROM ' +
          'dbo.Tab1 ' +
        'WHERE ' +
          '(' + @NomCamScelta + '= 4) ' +
      '), ' +
      'cte02 AS ' +
      '( ' +
        'SELECT ' +
          'Marca ' +
          ',SUM(Valore) AS Totale ' +
        'FROM ' +
          'cte01 ' +
        'GROUP BY ' +
          'Marca ' +
      ') ' +
      'SELECT ' +
        'Marca ' +
        ',Totale ' +
      'FROM ' +
        'cte02';
    
      EXEC(@SQLCommand);
    END
    GO

    Se la query diventerà complessa il debug non sarà semplice, è la soluzione "veloce" anche se non sono un fan di Dynamic SQL.

    Un'alternativa è quella di utilizzare un parametro intero che funge "switch" in funzione del quale eseguire una query piuttosto che l'altra con il nome della colonna in chiaro. La stored procedure diventerà molto "verbosa" ma sarà più chiara.. se il numero di colonne da condizionare aumenta, aumenterà di molto la "verbosità" della query.

    Fammi sapere quale soluzione adotterai :)

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn




    sabato 3 luglio 2021 21:11
    Moderatore
  • ....... se il numero di colonne da condizionare aumenta, aumenterà di molto la "verbosità" della query.

    Fammi sapere quale soluzione adotterai :)

    Ok,   perfetto è proprio quello che mi serviva .... :) ........

    In effetti il numero di colonne è notevole e anche il numero di parametri

    quindi ho usato il dynamic sql

    Ti chiederei pero una conferma perche non sono sicuro e non vorrei che ci siano delle controindicazioni che non conosco .......

    Io per scrivere la pate principale del  @SQLCommand   non ho usato la concatenazione con il segno + come mi hai indicato

    bensi sono andato semplicemente a capo generando quindi un'unico  STRINGONE  come abbozzato sotto

    posso avere dei problemi ???

    Grazie

    --   ............................................
    SET @SQLCommand =
    '
    WITH cte01 AS 
    ( 
    SELECT 
    Marca ,Valore 
    FROM dbo.Tab1 
    WHERE (' + @NomCamScelta + '= 4)
    ),
    
    cte02 AS 
    (
    SELECT 
    Marca, SUM(Valore) AS Totale FROM cte01 GROUP BY Marca
    )
    
    SELECT Marca, Totale FROM cte02 
    '
    --  ......................................................

    • Modificato Mancini, domenica 4 luglio 2021 21:35
    domenica 4 luglio 2021 21:29
  • Ciao,

    immaginavo che la query e il numero di colonne reali fossero di più :-)

    Per l’utilizzo dell'operatore "+" per concatenare le stringe.. lo preferisco perché evita la presenza di CRLF carriage return line feed CHR(13) + CHR(10) all'interno della stringa stessa ma non dovrebbero esserci controindicazioni.

    Le scrivo con "+" per questo motivo.

    Ciao, a presto!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn

    domenica 4 luglio 2021 22:50
    Moderatore