[SQL] Passare come parametro ORDER BY

[giustavalla]

Ho una stored procedure in cui c'è un SELECT .... ORDER BY .....
L'ORDER BY è fortemente parametrizzato.
Da programma (sviluppato nel buon vecchio VB6) io valorizzo i parametri


If TipoOrdine(QualeOrdine) = 0 Then
Senso = " ASC"
Else
Senso = " DESC"
End If
Select Case QualeOrdine
Case 0: strOrdine = "TabDateDip.RagioneSociale" + Senso + ",TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC"
Case 1: strOrdine = "TabDateDip.CognomeNomeDipendente" + Senso + ",TabDateDip.Codice ASC"
Case 2: strOrdine = "TabDateDip.DataSca" + Senso + ",TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC"
Case 3: strOrdine = "TabDateDip.TipoPrestazione" + Senso + ",TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC"
Case 4: strOrdine = "Codice" + Senso + ",TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC"
Case 5: strOrdine = "Luogo" + Senso + ",TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC"
Case 6: strOrdine = "NomeMedico" + Senso + ",TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC"
Case 7: strOrdine = "Stato" + Senso + ",TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC"
End Select

Set cmdB = New ADODB.Command
cmdB.ActiveConnection = cnn
cmdB.CommandType = adCmdStoredProc
cmdB.CommandText = "strOrganizzaGiornata"
cmdB.Parameters.Append cmdB.CreateParameter("strDataX", adVarChar, adParamInput, 10, strDataX)
cmdB.Parameters.Append cmdB.CreateParameter("strStato", adVarChar, adParamInput, 2, strStato)
cmdB.Parameters.Append cmdB.CreateParameter("strOrdine", adVarChar, adParamInput, 1000, strOrdine)
Set rsMese = cmdB.Execute
e nella stored procedure dovrei essere in grado di impostare il desiderato valore di ORDER BY.

SELECT TabDateDip.RagioneSociale,TabDateDip.CognomeNomeDi pendente,TabDateDip.DataSca,TabDateDip.TipoPrestaz ione,TabDateDip.Codice,TabDateDip.Luogo,TabDateDip .NomeMedico,
TabDateDip.Stato,TabDateDip.ID,TabDateDip.IDAziend a,TabDateDip.IDDipendente,TabDateDip.IDMedico,TabD ateDip.IDLuogoVisita,TabDateDip.DataPre,TabDateDip .Ora,TabDateDip.DataRegistrazione,
TabDateDip.InServizio,TabDateDip.DataEff,TabAziend e.DataSospesa FROM TabDateDip INNER JOIN TabAziende ON TabDateDip.IDAzienda = TabAziende.ID
WHERE
TabDateDip.DataSca<@strDataX
AND TabDateDip.InServizio='S'
AND TabDateDip.Stato=@strStato
ORDER BY ??????
--- TabDateDip.RagioneSociale ASC,
--- TabDateDip.CognomeNomeDipendente ASC,
--- TabDateDip.Codice ASC
Ho indicato con alcuni punti interrogativi il punto in cui mi son fermato, non sapendo come completare la query.
C'è modo di risolvere il problema ?
Grazie per l'attenzione.

[optime]

ovviamente devi passare un parametro che indichi ORDER BY cosa.

Poi te la cavi con


ORDER BY
CASE
WHEN <@parametro>='<qualcosa>' THEN <criterio_di_riordinamento>
WHEN <@parametro>='<qualcos'altro>' THEN <altro_criterio_di_riordinamento>
WHEN <@parametro>='<qualcos'altro_ancora>' THEN <ultimo_criterio_di_riordinamento>
END

facce sape' :popcorn:

[giustavalla]

Ho modificato la stored procedure seguendo le tue indicazioni ed annidiando 2 CASE. L'indentazione però non mi convince anche se all'interno di SSMA non compaiono errori. Tuttavia, pur passando i corretti parametri strSenso ('ASC' o 'DESC') e strParametro ('0', '1', '2', ecc) il risultato non cambia, ossia l'output è sempre il medesimo. Il perchè non lo so proprio.
Se esistesse (non lo so) la possibilità di eseguire la stored procedure passo passo riuscirei a capire dove fallisce.

USE [Medart_SA2]
GO
/****** Object: StoredProcedure [dbo].[strOrganizzaGiornata] Script Date: 11/07/2020 16:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---

ALTER PROCEDURE [dbo].[strOrganizzaGiornata]
@strDataX varchar(10),
@strStato varchar(2),
@strSenso varchar(4),
@strParametro varchar(1)
AS

BEGIN
SET NOCOUNT ON
SELECT TabDateDip.RagioneSociale,TabDateDip.CognomeNomeDi pendente,TabDateDip.DataSca,TabDateDip.TipoPrestaz ione,TabDateDip.Codice,TabDateDip.Luogo,TabDateDip .NomeMedico,
TabDateDip.Stato,TabDateDip.ID,TabDateDip.IDAziend a,TabDateDip.IDDipendente,TabDateDip.IDMedico,TabD ateDip.IDLuogoVisita,TabDateDip.DataPre,TabDateDip .Ora,TabDateDip.DataRegistrazione,
TabDateDip.InServizio,TabDateDip.DataEff,TabAziend e.DataSospesa FROM TabDateDip INNER JOIN TabAziende ON TabDateDip.IDAzienda = TabAziende.ID
WHERE TabDateDip.DataSca<@strDataX
AND TabDateDip.InServizio='S'
AND TabDateDip.Stato=@strStato
ORDER BY
CASE
WHEN @strSenso='ASC' THEN
CASE
WHEN @strParametro='0' THEN 'TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='1' THEN 'TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='2' THEN 'TabDateDip.DataSca ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='3' THEN 'TabDateDip.TipoPrestazione ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='4' THEN 'TabDateDip.Codice ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC'
WHEN @strParametro='5' THEN 'TabDateDip.Luogo ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='6' THEN 'TabDateDip.NomeMedico ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='7' THEN 'TabDateDip.Stato ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
END
WHEN @strSenso='DESC' THEN
CASE
WHEN @strParametro='0' THEN 'TabDateDip.RagioneSociale DESC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='1' THEN 'TabDateDip.CognomeNomeDipendente DESC,TabDateDip.Codice ASC'
WHEN @strParametro='2' THEN 'TabDateDip.DataSca DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='3' THEN 'TabDateDip.TipoPrestazione DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='4' THEN 'TabDateDip.Codice DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC'
WHEN @strParametro='5' THEN 'TabDateDip.Luogo DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='6' THEN 'TabDateDip.NomeMedico DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
WHEN @strParametro='7' THEN 'TabDateDip.Stato DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC'
END
END

END

[optime]

I valori dopo il THEN non vanno tra apici

[giustavalla]

30033
Giusto eliminare gli apici ma lo stesso SSMS segnala un errore. Da quando in qua ASC è sbagliato ?

[optime]

Posta l’errore e la sp

[optime]

vista l'immagine; forse ti conviene pensare a una SQL dinamica, vista la varietà dei riordinamenti

[giustavalla]

Nel precedente post ho allegato un'immagine con evidenziati gli errori segnalati da SSMS.
Ho poi fatto qualche modifica e la SP funziona se mi limito ad un tipo di ordinamento semplice semplice.
La seguente versione della SP viceversa mi segnala (in SSMS) un errore rosso in corrispondenza della parola ASC nella riga
WHEN @intParametro=0 THEN TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
e prosegue segnalandomi un errore rosso in corrisponenza della parola WHEN della riga successiva.
Come ho detto non mi convince l'indentazione.
Questo comunque è il messaggio di errore dell'ultima versione della SP
Messaggio 156, livello 15, stato 1, procedura strOrganizzaGiornata, riga 21 [riga iniziale del batch 9]
Sintassi non corretta in prossimità della parola chiave 'ASC'.
Eccola :

USE [Medart_SA2]
GO
/****** Object: StoredProcedure [dbo].[strOrganizzaGiornata] Script Date: 12/07/2020 09:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[strOrganizzaGiornata]
@strDataX varchar(10),
@strStato varchar(2),
@strSenso varchar(4),
@intParametro int
AS
BEGIN
SET NOCOUNT ON
SELECT TabDateDip.RagioneSociale,TabDateDip.CognomeNomeDi pendente,TabDateDip.DataSca,TabDateDip.TipoPrestaz ione,TabDateDip.Codice,TabDateDip.Luogo,TabDateDip .NomeMedico,
TabDateDip.Stato,TabDateDip.ID,TabDateDip.IDAziend a,TabDateDip.IDDipendente,TabDateDip.IDMedico,TabD ateDip.IDLuogoVisita,TabDateDip.DataPre,TabDateDip .Ora,TabDateDip.DataRegistrazione,
TabDateDip.InServizio,TabDateDip.DataEff,TabAziend e.DataSospesa FROM TabDateDip INNER JOIN TabAziende ON TabDateDip.IDAzienda = TabAziende.ID
WHERE TabDateDip.DataSca<@strDataX
AND TabDateDip.InServizio='S'
AND TabDateDip.Stato=@strStato
ORDER BY
CASE
WHEN @strSenso='ASC' THEN
CASE
WHEN @intParametro=0 THEN TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=1 THEN TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=2 THEN TabDateDip.DataSca ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=3 THEN TabDateDip.TipoPrestazione ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=4 THEN TabDateDip.Codice ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC
WHEN @intParametro=5 THEN TabDateDip.Luogo ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=6 THEN TabDateDip.NomeMedico ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=7 THEN TabDateDip.Stato ASC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
END
WHEN @strSenso='DESC' THEN
CASE
WHEN @intParametro=0 THEN TabDateDip.RagioneSociale DESC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=1 THEN TabDateDip.CognomeNomeDipendente DESC,TabDateDip.Codice ASC
WHEN @intParametro=2 THEN TabDateDip.DataSca DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=3 THEN TabDateDip.TipoPrestazione DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=4 THEN TabDateDip.Codice DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC
WHEN @intParametro=5 THEN TabDateDip.Luogo DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=6 THEN TabDateDip.NomeMedico DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
WHEN @intParametro=7 THEN TabDateDip.Stato DESC,TabDateDip.RagioneSociale ASC,TabDateDip.CognomeNomeDipendente ASC,TabDateDip.Codice ASC
END
END

END

[optime]

prova questa


USE [Medart_SA2]GO
/****** Object: StoredProcedure [dbo].[strOrganizzaGiornata] Script Date: 11/07/2020 16:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---


ALTER PROCEDURE [dbo].[strOrganizzaGiornata]
@strDataX varchar(10),
@strStato varchar(2),
@strSenso varchar(4),
@strParametro varchar(1)
AS


BEGIN
SET NOCOUNT ON

-- per evitare SQL Injection, controlla che il parametro sia valido
IF @strParametro NOT IN ('0', '1','2','3','4','5','6','7')
BEGIN
RAISERROR('@strParametro non previsto: %s', 11, 1, @strParametro);
RETURN -1;
END


-- per evitare SQL Injection, controlla che la direzione dell'ordinamento sia valido
IF UPPER(@strSenso) NOT IN ('ASC','DESC')
BEGIN
RAISERROR('@strSenso non previsto: %s', 11, 1, @strSenso);
RETURN -1;
END

DECLARE @SQL AS NVARCHAR(MAX) -- contiene la stringa da eseguire
DECLARE @ORB AS NVARCHAR(MAX) -- contiene l'ordinamento

-- costruisce la query
SET @SQL=N'SELECT
D.RagioneSociale,
D.CognomeNomeDipendente,
D.DataSca,
D.TipoPrestazione,
D.Codice,
D.Luogo,
D.NomeMedico,
D.Stato,
D.ID,
D.IDAzienda,
D.IDDipendente,
D.IDMedico,
D.IDLuogoVisita,
D.DataPre,
D.Ora,
D.DataRegistrazione,
D.InServizio,
D.DataEff,
A.DataSospesa
FROM
TabDateDip D
INNER JOIN TabAziende A ON D.IDAzienda = A.ID
WHERE 1=1
AND D.DataSca<@strDataX
AND D.InServizio=''S''
AND D.Stato=@strStato
ORDER BY '


-- stabilisce l'ordinamento in base al parametro passato
IF @strParametro='0' BEGIN SET @ORB=N'D.RagioneSociale @verso, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='1' BEGIN SET @ORB=N'D.CognomeNomeDipendente @verso, D.Codice' END
IF @strParametro='2' BEGIN SET @ORB=N'D.Data @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='3' BEGIN SET @ORB=N'D.TipoPrestazione @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='4' BEGIN SET @ORB=N'D.Codice @verso, D.RagioneSociale, D.CognomeNomeDipendente' END
IF @strParametro='5' BEGIN SET @ORB=N'D.Luogo @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='6' BEGIN SET @ORB=N'D.NomeMedico @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='7' BEGIN SET @ORB=N'D.Stato @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END


-- cambia il verso dove è variabile (dove non è variabile resta ASC come default)
SET @ORB=REPLACE(@ORB, '@verso', @strSenso)


-- ricostruisce la query con anche l'ordinamento
SET @SQL=@SQL + ' ' + @ORB


-- la stampa
PRINT @SQL

-- la esegue
EXEC sp_executesql @SQL


END

[giustavalla]

Ho fatto un paio di piccole aggiunte e la SP funziona egregiamente. Purtroppo la velocità di esecuzione è praticamente identica a quella di una analogo query tradizionale che prevede l'utilizzo di un recordset.
L'utilizzo di una SP era finalizzato a velocizzare sensibilmente una certa procedura. Con il metodo tradizionale essa impiegava circa 17 secondi ad essere eseguite, mentre con la SP ci impiega .... 17 secondi. E questo anche se la si richiama più volte, sperando nell'intelligenza del motore di SQL Server; speranza vana, almeno in questo caso.
Resta, da parte mia, il fatto che ho appreso, grazie al tuo aiuto, qualcosa circa le SP.
Riporto qui di sotto la SP in questione

USE [Medart_SA2]
GO
/****** Object: StoredProcedure [dbo].[strOrganizzaGiornata2] Script Date: 12/07/2020 16:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---


ALTER PROCEDURE [dbo].[strOrganizzaGiornata2]
@strDataX varchar(10),
@strStato varchar(2) ,
@strSenso varchar(4) ,
@strParametro varchar(1)
AS


BEGIN
SET NOCOUNT ON

-- per evitare SQL Injection, controlla che il parametro sia valido
IF @strParametro NOT IN ('0', '1','2','3','4','5','6','7')
BEGIN
RAISERROR('@strParametro non previsto: %s', 11, 1, @strParametro);
RETURN -1;
END

-- per evitare SQL Injection, controlla che la direzione dell'ordinamento sia valido
IF UPPER(@strSenso) NOT IN ('ASC','DESC')
BEGIN
RAISERROR('@strSenso non previsto: %s', 11, 1, @strSenso);
RETURN -1;
END

DECLARE @SQL AS NVARCHAR(MAX) -- contiene la stringa da eseguire
DECLARE @ORB AS NVARCHAR(MAX) -- contiene l'ordinamento

-- costruisce la query
SET @SQL=N'SELECT
D.RagioneSociale,
D.CognomeNomeDipendente,
D.DataSca,
D.TipoPrestazione,
D.Codice,
D.Luogo,
D.NomeMedico,
D.Stato,
D.ID,
D.IDAzienda,
D.IDDipendente,
D.IDMedico,
D.IDLuogoVisita,
D.DataPre,
D.Ora,
D.DataRegistrazione,
D.InServizio,
D.DataEff,
A.DataSospesa
FROM
TabDateDip D
INNER JOIN TabAziende A ON D.IDAzienda = A.ID
WHERE 1=1
AND D.DataSca<''@datta''
AND D.Stato=''@stato''
AND D.InServizio=''S''
ORDER BY '

-- stabilisce l'ordinamento in base al parametro passato
IF @strParametro='0' BEGIN SET @ORB=N'D.RagioneSociale @verso, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='1' BEGIN SET @ORB=N'D.CognomeNomeDipendente @verso, D.Codice' END
IF @strParametro='2' BEGIN SET @ORB=N'D.DataSca @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END --- modificato
IF @strParametro='3' BEGIN SET @ORB=N'D.TipoPrestazione @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='4' BEGIN SET @ORB=N'D.Codice @verso, D.RagioneSociale, D.CognomeNomeDipendente' END
IF @strParametro='5' BEGIN SET @ORB=N'D.Luogo @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='6' BEGIN SET @ORB=N'D.NomeMedico @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END
IF @strParametro='7' BEGIN SET @ORB=N'D.Stato @verso, D.RagioneSociale, D.CognomeNomeDipendente, D.Codice' END

-- cambia il verso dove è variabile (dove non è variabile resta ASC come default)
SET @ORB=REPLACE(@ORB, '@verso', @strSenso)
SET @SQL=REPLACE(@SQL, '@datta', @strDataX) --- aggiunto
SET @SQL=REPLACE(@SQL, '@stato', @strStato) --- aggiunto
-- ricostruisce la query con anche l'ordinamento
SET @SQL=@SQL + ' ' + @ORB

-- la stampa
PRINT @SQL

-- la esegue
EXEC sp_executesql @SQL

END