Mind Your Manners
-
I really enjoyed the article, thank you!
It's probaly a little late to mention this but, another thing that would be very useful as public knowledge is the <Pre> tag.
Using the <Pre> tag keeps your code formating and will enable you to avoid smiley faces in your code.
<Pre>
CODE
</Pre>
SQL guy and Houston Magician
-
Can't make it work...
Care to post an exemple Robert?
-
Sure:
Both blocks of code are formatted the same when pasted into the forum. The bottom block is wrapped in <PRE> </PRE>
Code taken from another thread:
SELECT a.*,
(CASE WHEN (SELECT TOP 1 b.effDate-1
FROM #table b
WHERE b.effDate > a.effDate AND a.currencycode = b.currencycode) IS NULL
THEN (SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, -1))
ELSE (SELECT TOP 1 b.effDate-1
FROM #table b
WHERE b.effDate > a.effDate and a.currencycode = b.currencycode)
END) AS 'discDate'
FROM #table a
SELECT a.*, (CASE WHEN (SELECT TOP 1 b.effDate-1 FROM #table b WHERE b.effDate > a.effDate AND a.currencycode = b.currencycode) IS NULL THEN (SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, -1)) ELSE (SELECT TOP 1 b.effDate-1 FROM #table b WHERE b.effDate > a.effDate and a.currencycode = b.currencycode) END) AS 'discDate' FROM #table aIt should look like this in the source window
<PRE>
SELECT a.*, (CASE WHEN (SELECT TOP 1 b.effDate-1 FROM #table b WHERE b.effDate > a.effDate AND a.currencycode = b.currencycode) IS NULL THEN (SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, -1)) ELSE (SELECT TOP 1 b.effDate-1 FROM #table b WHERE b.effDate > a.effDate and a.currencycode = b.currencycode) END) AS 'discDate' FROM #table a</PRE>
SQL guy and Houston Magician
-
My bad!
The pre tag won't avoid the smileys. (but your code looks prettier!)
Sorry
-Robert
SQL guy and Houston Magician
-
ok I found my problem.
How do I keep the formatting from changing between QA or EM and the posting textbox??? The tabs go from 4-5 spaces to 1
. -
It might be worth changing your settings in QA (this will help)
Tools->Options->Editor
Check the Save tabs as spaces box
SQL guy and Houston Magician
-
I can only make it work in netscape... maybe it's because I have wysiwyg turned on in IE???
Look how pretty that is... just too bad that some formatting is lost.
Any way to have the colors added to that without having sql server 2005 installed??
CREATE PROCEDURE [dbo].[SPNSearchAchats] --recherche par rapport à l'entete @NoCommande AS INT = NULL -- # de commande , @NomFournisseur AS VARCHAR(50) = NULL -- nom du fournisseur , @DateCommandeD AS DATETIME = NULL -- date début de la commande , @DateCommandeF AS DATETIME = NULL -- date fin de la commande , @DateLivraisonD AS DATETIME = NULL -- date début de la Livraison , @DateLivraisonF AS DATETIME = NULL -- date fin de la Livraison --recherche par rapport au pièces , @CodeDePiece AS VARCHAR(10) = NULL -- Code de la pièce , @DescPiece AS VARCHAR(50) = NULL -- description du produit , @NoBonTravail AS VARCHAR(10) = NULL -- # bon travail où envoyer la pièce , @PoClient AS VARCHAR(50) = NULL -- # de commande du client , @NomClient AS VARCHAR(50) = NULL -- nom du client , @NoContrat AS VARCHAR(10) = NULL -- # de contrat , @Complete AS BIT = NULL -- Si la réception est complète , @NoPackingSlip AS VARCHAR(50) = NULL -- # packing slip , @NoFacture AS VARCHAR(50) = NULL -- # facture du fournisseur , @NoBillTransport AS VARCHAR(50) = NULL -- # bil de transport , @DateReceptionD AS DATETIME = NULL -- date début de la réception , @DateReceptionF AS DATETIME = NULL -- date début de la réception --paramètres pour le type de recherche , @Location AS TINYINT = 0 -- Type recherche : au début du champ(1), n'importe où (3), match exact (0) , @OrderBy AS VARCHAR(15) = 'NoCommande' -- colonne choisie pour le tri , @ALLCriterias AS BIT = 0 -- trouver tous les critères où un seul WITH RECOMPILE AS SET NOCOUNT ON DECLARE @StartWC AS VARCHAR(1) DECLARE @EndWC AS VARCHAR(1) DECLARE @bAnyParamIsSet AS BIT DECLARE @bAnyDateCommande AS BIT DECLARE @bAnyDateLivraison AS BIT DECLARE @bAnyDateReception AS BIT DECLARE @bAnyEnteteParamIsSet AS BIT DECLARE @bAnyPieceParamIsSet AS BIT SELECT @NoCommande = NULLIF(@NoCommande, '') , @NomFournisseur = NULLIF(@NomFournisseur, '') , @NomClient = NULLIF(@NomClient, '') , @DateCommandeD = NULLIF(@DateCommandeD, '') , @DateCommandeF = NULLIF(@DateCommandeF, '') , @DateLivraisonD = NULLIF(@DateLivraisonD, '') , @DateLivraisonF = NULLIF(@DateLivraisonF, '') , @DateReceptionD = NULLIF(@DateReceptionD, '') , @DateReceptionF = NULLIF(@DateReceptionF, '') , @CodeDePiece = NULLIF(@CodeDePiece, '') , @DescPiece = NULLIF(@DescPiece, '') , @NoBonTravail = NULLIF(@NoBonTravail, '') , @NoContrat = NULLIF(@NoContrat, '') , @PoClient = NULLIF(@PoClient, '') , @NoPackingSlip = NULLIF(@NoPackingSlip, '') , @NoFacture = NULLIF(@NoFacture, '') , @NoBillTransport = NULLIF(@NoBillTransport, '') , @OrderBy = NULLIF(@OrderBy, 'NoCommande') , @StartWC = CASE WHEN @Location & 2 = 2 THEN '%' ELSE '' END , @EndWC = CASE WHEN @Location & 1 = 1 THEN '%' ELSE '' END , @bAnyEnteteParamIsSet = CAST(LEN( ISNULL(CAST(@NoCommande AS VARCHAR(10)), '') + ISNULL(@NomFournisseur, '') + ISNULL(CAST(@DateCommandeD AS VARCHAR(30)), '') + ISNULL(CAST(@DateCommandeF AS VARCHAR(30)), '') + ISNULL(CAST(@DateLivraisonD AS VARCHAR(30)), '') + ISNULL(CAST(@DateLivraisonF AS VARCHAR(30)), '') ) AS BIT) , @bAnyPieceParamIsSet = CAST(LEN( ISNULL(CAST(@Complete AS VARCHAR(1)),'') + ISNULL(@NomClient, '') + ISNULL(@CodeDePiece, '') + ISNULL(@DescPiece, '') + ISNULL(@NoBonTravail, '') + ISNULL(@NoContrat, '') + ISNULL(@PoClient, '') + ISNULL(@NoPackingSlip, '') + ISNULL(@NoFacture, '') + ISNULL(@NoBillTransport, '') + ISNULL(CAST(@DateReceptionD AS VARCHAR(30)), '') + ISNULL(CAST(@DateReceptionF AS VARCHAR(30)), '') ) AS BIT) , @bAnyDateCommande = CAST(ISDATE(@DateCommandeD) + ISDATE(@DateCommandeF) AS BIT) , @bAnyDateLivraison = CAST(ISDATE(@DateLivraisonD) + ISDATE(@DateLivraisonF) AS BIT) , @bAnyDateReception = CAST(ISDATE(@DateReceptionD) + ISDATE(@DateReceptionF) AS BIT) SET @bAnyParamIsSet = @bAnyEnteteParamIsSet | @bAnyPieceParamIsSet SELECT @DateCommandeD = ISNULL(@DateCommandeD, 0) , @DateCommandeF = ISNULL(@DateCommandeF, DATEADD(D, 1, GETDATE())) , @DateLivraisonD = ISNULL(@DateLivraisonD, 0) , @DateLivraisonF = ISNULL(@DateLivraisonF, DATEADD(D, 1, GETDATE())) , @DateReceptionD = ISNULL(@DateReceptionD, 0) , @DateReceptionF = ISNULL(@DateReceptionF, DATEADD(D, 1, GETDATE())) SELECT [NO DE COMMANDE] , [NO DE CONTRAT] , [Livre a] , [Nom liv] , FNOM , [DATE DE COMMNANDE] , LIVRAISON , CONTACT FROM dbo.ENTETE E WHERE @bAnyParamIsSet = 1 AND --allows sql server not to do any work if no param is set 0 < CASE WHEN @ALLCriterias = 0 THEN --retourner la ligne si un seul des critères est valide CASE WHEN NOT @NoCommande IS NULL AND E.[NO DE COMMANDE] LIKE @StartWC + CONVERT(VARCHAR(10), @NoCommande) + @EndWC THEN 1 WHEN NOT @NomFournisseur IS NULL AND E.FNOM LIKE @StartWC + @NomFournisseur + @EndWC THEN 1 WHEN @bAnyDateCommande = 1 AND E.[DATE DE COMMNANDE] BETWEEN @DateCommandeD AND @DateCommandeF THEN 1 WHEN @bAnyDateLivraison = 1 AND E.LIVRAISON BETWEEN @DateLivraisonD AND @DateLivraisonF THEN 1 WHEN @bAnyPieceParamIsSet = 1 THEN CASE WHEN EXISTS ( SELECT * FROM dbo.DETAIL D WHERE D.[NO DE COMMANDE] = E.[NO DE COMMANDE] AND @bAnyParamIsSet = 1 AND 0 < CASE WHEN NOT @NoContrat IS NULL AND D.[NO DE CONTRAT] LIKE @StartWC + @NoContrat + @EndWC THEN 1 WHEN NOT @PoClient IS NULL AND D.[PO CLIENT] LIKE @StartWC + @PoClient + @EndWC THEN 1 WHEN NOT @NomClient IS NULL AND D.[Nom du Client] LIKE @StartWC + @NomClient + @EndWC THEN 1 WHEN NOT @CodeDePiece IS NULL AND D.PNUM LIKE @StartWC + @CodeDePiece + @EndWC THEN 1 WHEN NOT @DescPiece IS NULL AND D.PNOM LIKE @StartWC + @DescPiece + @EndWC THEN 1 WHEN NOT @NoBonTravail IS NULL AND D.FT LIKE @StartWC + @NoBonTravail + @EndWC THEN 1 WHEN NOT @NoPackingSlip IS NULL AND ',' + ISNULL([PSLIP 1], '') + ',' + ISNULL([PSLIP 2], '') + ',' + ISNULL([PSLIP 3], '') + ',' + ISNULL([PSLIP 4], '') + ',' + ISNULL([PSLIP 5], '') + ',' LIKE '%,' + @StartWC + @NoPackingSlip + @EndWC + ',%' THEN 1 WHEN NOT @NoFacture IS NULL AND ',' + ISNULL([FACTURE 1], '') + ',' + ISNULL([FACTURE 2], '') + ',' + ISNULL([FACTURE 3], '') + ',' + ISNULL([FACTURE 4], '') + ',' + ISNULL([FACTURE 5], '') + ',' LIKE '%,' + @StartWC + @NoFacture + @EndWC + ',%' THEN 1 WHEN NOT @NoBillTransport IS NULL AND',' + ISNULL([BILL TRANS 1], '') + ',' + ISNULL([BILL TRANS 2], '') + ',' + ISNULL([BILL TRANS 3], '') + ',' + ISNULL([BILL TRANS 4], '') + ',' + ISNULL([BILL TRANS 5], '') + ',' LIKE '%,' + @StartWC + @NoBillTransport + @EndWC + ',%' THEN 1 WHEN 0 ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1 WHEN @Complete = 1 AND QUANTITÉ <= ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1 ELSE 0 END THEN 1 WHEN 0 < CASE WHEN @bAnyDateReception = 0 THEN 0 WHEN date_de_reception1 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception2 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception3 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception4 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception5 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 ELSE 0 END THEN 1 ELSE 0 END ) THEN 1 ELSE 0 END ELSE 0 END ELSE --@ALLCriterias = 1 --retourner la ligne si TOUS les critères sont valides CASE WHEN (@NoCommande IS NULL OR E.[NO DE COMMANDE] LIKE @StartWC + CONVERT(VARCHAR(10), @NoCommande) + @EndWC ) AND (@NomFournisseur IS NULL OR E.FNOM LIKE @StartWC + @NomFournisseur + @EndWC ) AND (@bAnyDateCommande = 0 OR E.[DATE DE COMMNANDE] BETWEEN @DateCommandeD AND @DateCommandeF ) AND (@bAnyDateLivraison = 0 OR E.LIVRAISON BETWEEN @DateLivraisonD AND @DateLivraisonF ) AND 0 < CASE WHEN @bAnyPieceParamIsSet = 0 THEN 1 WHEN EXISTS ( SELECT * FROM dbo.DETAIL D WHERE D.[NO DE COMMANDE] = E.[NO DE COMMANDE] AND @bAnyParamIsSet = 1 AND 0 < CASE WHEN (@NoContrat IS NULL OR D.[NO DE CONTRAT] LIKE @StartWC + @NoContrat + @EndWC ) AND (@PoClient IS NULL OR D.[PO CLIENT] LIKE @StartWC + @PoClient + @EndWC ) AND (@NomClient IS NULL OR D.[NOM DU CLIENT] LIKE @StartWC + @NomClient + @EndWC ) AND (@CodeDePiece IS NULL OR D.PNUM LIKE @StartWC + @CodeDePiece + @EndWC ) AND (@DescPiece IS NULL OR D.PNOM LIKE @StartWC + @DescPiece + @EndWC ) AND (@NoBonTravail IS NULL OR D.FT LIKE @StartWC + @NoBonTravail + @EndWC ) AND (@NoPackingSlip IS NULL OR ',' + ISNULL([PSLIP 1], '') + ',' + ISNULL([PSLIP 2], '') + ',' + ISNULL([PSLIP 3], '') + ',' + ISNULL([PSLIP 4], '') + ',' + ISNULL([PSLIP 5], '') + ',' LIKE '%,' + @StartWC + @NoPackingSlip + @EndWC + ',%' ) AND (@NoFacture IS NULL OR ',' + ISNULL([FACTURE 1], '') + ',' + ISNULL([FACTURE 2], '') + ',' + ISNULL([FACTURE 3], '') + ',' + ISNULL([FACTURE 4], '') + ',' + ISNULL([FACTURE 5], '') + ',' LIKE '%,' + @StartWC + @NoFacture + @EndWC + ',%' ) AND (@NoBillTransport IS NULL OR ',' + ISNULL([BILL TRANS 1], '') + ',' + ISNULL([BILL TRANS 2], '') + ',' + ISNULL([BILL TRANS 3], '') + ',' + ISNULL([BILL TRANS 4], '') + ',' + ISNULL([BILL TRANS 5], '') + ',' LIKE '%,' + @StartWC + @NoBillTransport + @EndWC + ',%' ) AND 0 ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1 WHEN @Complete = 1 AND QUANTITÉ <= ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1 ELSE 0 END AND 0 < CASE WHEN @bAnyDateReception = 0 THEN 1 WHEN date_de_reception1 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception2 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception3 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception4 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 WHEN date_de_reception5 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1 ELSE 0 END THEN 1 ELSE 0 END ) THEN 1 ELSE 0 END THEN 1 ELSE 0 END END ORDER BY CASE WHEN @OrderBy = 'NoCommande' THEN E.[NO DE COMMANDE] ELSE NULL END , CASE WHEN @OrderBy = 'NoContrat' THEN E.[NO DE CONTRAT] ELSE NULL END , CASE WHEN @OrderBy = 'NomFournisseur' THEN E.FNOM ELSE NULL END , CASE WHEN @OrderBy = 'NomClient' THEN E.[Nom liv] ELSE NULL END , CASE WHEN @OrderBy = 'DateCommande' THEN E.[DATE DE COMMNANDE] ELSE NULL END , CASE WHEN @OrderBy = 'Contact' THEN E.CONTACT ELSE NULL END , CASE WHEN @OrderBy = 'DateLivraison' THEN E.LIVRAISON ELSE NULL END , CASE WHEN @OrderBy = 'NoClient' THEN E.[Livre a] ELSE NULL END SET NOCOUNT OFF GO
-
Check out Simple-talk's SQL prettifier on http://www.simple-talk.com/sql/sql-tools/download-sql-prettifier/ It's very cool!
it's basically a SQL proc that generates html. The only downside is the proc you posted is too wide and it wraps in an ugly way. Worth a look though (and you don't need to use pre!)
-Robert
P.S. here is an example
SELECT a.*,(CASE WHEN (SELECT TOP 1 b.effDate-1
FROM #table b
WHERE b.effDate > a.effDate AND a.currencycode = b.currencycode) IS NULL
THEN (SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, -1)
)
ELSE (SELECT TOP 1 b.effDate-1
FROM #table b
WHERE b.effDate > a.effDate AND a.currencycode = b.currencycode)
END) AS 'discDate'
FROM #table
SQL guy and Houston Magician
-
I guess there is an online example here:http://www.simple-talk.com/prettifier/
SQL guy and Houston Magician
-
It's a good start... but it still has work to do. Also when using wysiwyg in IE, you lose all the formatting anyways. At least I got a starting point now
.CREATE PROCEDURE [dbo].[SPNSearchAchats]--recherche par rapport À l'entete
@NoCommande AS INT = NULL -- # de commande
, @NomFournisseur AS VARCHAR(50) = NULL -- nom du fournisseur
, @DateCommandeD AS DATETIME = NULL -- date dÉbut de la commande
, @DateCommandeF AS DATETIME = NULL -- date fin de la commande
, @DateLivraisonD AS DATETIME = NULL -- date dÉbut de la Livraison
, @DateLivraisonF AS DATETIME = NULL -- date fin de la Livraison
--recherche par rapport au piÈces
, @CodeDePiece AS VARCHAR(10) = NULL -- Code de la piÈce
, @DescPiece AS VARCHAR(50) = NULL -- description du produit
, @NoBonTravail AS VARCHAR(10) = NULL -- # bon travail oÙ envoyer la piÈce
, @PoClient AS VARCHAR(50) = NULL -- # de commande du client
, @NomClient AS VARCHAR(50) = NULL -- nom du client
, @NoContrat AS VARCHAR(10) = NULL -- # de contrat
, @Complete AS BIT = NULL -- Si la rÉception est complÈte
, @NoPackingSlip AS VARCHAR(50) = NULL -- # packing slip
, @NoFacture AS VARCHAR(50) = NULL -- # facture du fournisseur
, @NoBillTransport AS VARCHAR(50) = NULL -- # bil de transport
, @DateReceptionD AS DATETIME = NULL -- date dÉbut de la rÉception
, @DateReceptionF AS DATETIME = NULL -- date dÉbut de la rÉception
--paramÈtres pour le type de recherche
, @Location AS TINYINT = 0 -- Type recherche : au dÉbut du champ(1), n'importe oÙ (3), match exact (0)
, @OrderBy AS VARCHAR(15) = 'NoCommande' -- colonne choisie pour le tri
, @ALLCriterias AS BIT = 0 -- trouver tous les critÈres oÙ un seul
WITH RECOMPILE
AS
SET NOCOUNT ON
DECLARE @StartWC AS VARCHAR(1)
DECLARE @EndWC AS VARCHAR(1)
DECLARE @bAnyParamIsSet AS BIT
DECLARE @bAnyDateCommande AS BIT
DECLARE @bAnyDateLivraison AS BIT
DECLARE @bAnyDateReception AS BIT
DECLARE @bAnyEnteteParamIsSet AS BIT
DECLARE @bAnyPieceParamIsSet AS BIT
SELECT
@NoCommande = NULLIF(@NoCommande, '')
, @NomFournisseur = NULLIF(@NomFournisseur, '')
, @NomClient = NULLIF(@NomClient, '')
, @DateCommandeD = NULLIF(@DateCommandeD, '')
, @DateCommandeF = NULLIF(@DateCommandeF, '')
, @DateLivraisonD = NULLIF(@DateLivraisonD, '')
, @DateLivraisonF = NULLIF(@DateLivraisonF, '')
, @DateReceptionD = NULLIF(@DateReceptionD, '')
, @DateReceptionF = NULLIF(@DateReceptionF, '')
, @CodeDePiece = NULLIF(@CodeDePiece, '')
, @DescPiece = NULLIF(@DescPiece, '')
, @NoBonTravail = NULLIF(@NoBonTravail, '')
, @NoContrat = NULLIF(@NoContrat, '')
, @PoClient = NULLIF(@PoClient, '')
, @NoPackingSlip = NULLIF(@NoPackingSlip, '')
, @NoFacture = NULLIF(@NoFacture, '')
, @NoBillTransport = NULLIF(@NoBillTransport, '')
, @OrderBy = NULLIF(@OrderBy, 'NoCommande')
, @StartWC = CASE WHEN @Location & 2 = 2 THEN '%' ELSE '' END
, @EndWC = CASE WHEN @Location & 1 = 1 THEN '%' ELSE '' END
, @bAnyEnteteParamIsSet = CAST(LEN(
ISNULL(CAST(@NoCommande AS VARCHAR(10)), '')
+ ISNULL(@NomFournisseur, '')
+ ISNULL(CAST(@DateCommandeD AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateCommandeF AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateLivraisonD AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateLivraisonF AS VARCHAR(30)), '')
 
AS BIT), @bAnyPieceParamIsSet = CAST(LEN(
ISNULL(CAST(@Complete AS VARCHAR(1)),'')
+ ISNULL(@NomClient, '')
+ ISNULL(@CodeDePiece, '')
+ ISNULL(@DescPiece, '')
+ ISNULL(@NoBonTravail, '')
+ ISNULL(@NoContrat, '')
+ ISNULL(@PoClient, '')
+ ISNULL(@NoPackingSlip, '')
+ ISNULL(@NoFacture, '')
+ ISNULL(@NoBillTransport, '')
+ ISNULL(CAST(@DateReceptionD AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateReceptionF AS VARCHAR(30)), '')
 
AS BIT), @bAnyDateCommande = CAST(ISDATE(@DateCommandeD) + ISDATE(@DateCommandeF) AS BIT)
, @bAnyDateLivraison = CAST(ISDATE(@DateLivraisonD) + ISDATE(@DateLivraisonF) AS BIT)
, @bAnyDateReception = CAST(ISDATE(@DateReceptionD) + ISDATE(@DateReceptionF) AS BIT)
SET @bAnyParamIsSet = @bAnyEnteteParamIsSet | @bAnyPieceParamIsSet
SELECT
@DateCommandeD = ISNULL(@DateCommandeD, 0)
, @DateCommandeF = ISNULL(@DateCommandeF, DATEADD(D, 1, GETDATE()))
, @DateLivraisonD = ISNULL(@DateLivraisonD, 0)
, @DateLivraisonF = ISNULL(@DateLivraisonF, DATEADD(D, 1, GETDATE()))
, @DateReceptionD = ISNULL(@DateReceptionD, 0)
, @DateReceptionF = ISNULL(@DateReceptionF, DATEADD(D, 1, GETDATE()))
SELECT [NO DE COMMANDE]
, [NO DE CONTRAT]
, [Livre a]
, [Nom liv]
, FNOM
, [DATE DE COMMNANDE]
, LIVRAISON
, CONTACT
FROM dbo.ENTETE E
WHERE
@bAnyParamIsSet = 1 AND --allows sql server not to do any work if no param is set
0 <
CASE WHEN @ALLCriterias = 0 THEN --retourner la ligne si un seul des critÈres est valide
CASE
WHEN NOT @NoCommande IS NULL AND E.[NO DE COMMANDE] LIKE @StartWC + CONVERT(VARCHAR(10), @NoCommande) + @EndWC THEN 1
WHEN NOT @NomFournisseur IS NULL AND E.FNOM LIKE @StartWC + @NomFournisseur + @EndWC THEN 1
WHEN @bAnyDateCommande = 1 AND E.[DATE DE COMMNANDE] BETWEEN @DateCommandeD AND @DateCommandeF THEN 1
WHEN @bAnyDateLivraison = 1 AND E.LIVRAISON BETWEEN @DateLivraisonD AND @DateLivraisonF THEN 1
WHEN @bAnyPieceParamIsSet = 1 THEN
CASE WHEN EXISTS (
SELECT
*
FROM dbo.DETAIL D
WHERE D.[NO DE COMMANDE] = E.[NO DE COMMANDE]
AND @bAnyParamIsSet = 1
AND 0 <
CASE WHEN NOT @NoContrat IS NULL AND D.[NO DE CONTRAT] LIKE @StartWC + @NoContrat + @EndWC THEN 1
WHEN NOT @PoClient IS NULL AND D.[PO CLIENT] LIKE @StartWC + @PoClient + @EndWC THEN 1
WHEN NOT @NomClient IS NULL AND D.[Nom du Client] LIKE @StartWC + @NomClient + @EndWC THEN 1
WHEN NOT @CodeDePiece IS NULL AND D.PNUM LIKE @StartWC + @CodeDePiece + @EndWC THEN 1
WHEN NOT @DescPiece IS NULL AND D.PNOM LIKE @StartWC + @DescPiece + @EndWC THEN 1
WHEN NOT @NoBonTravail IS NULL AND D.FT LIKE @StartWC + @NoBonTravail + @EndWC THEN 1
WHEN NOT @NoPackingSlip IS NULL AND ',' + ISNULL([PSLIP 1], '') + ',' + ISNULL([PSLIP 2], '') + ',' + ISNULL([PSLIP 3], '') + ',' + ISNULL([PSLIP 4], '') + ',' + ISNULL([PSLIP 5], '') + ',' LIKE '%,' + @StartWC + @NoPackingSlip + @EndWC + ',%' THEN 1
WHEN NOT @NoFacture IS NULL AND ',' + ISNULL([FACTURE 1], '') + ',' + ISNULL([FACTURE 2], '') + ',' + ISNULL([FACTURE 3], '') + ',' + ISNULL([FACTURE 4], '') + ',' + ISNULL([FACTURE 5], '') + ',' LIKE '%,' + @StartWC + @NoFacture + @EndWC + ',%' THEN 1
WHEN NOT @NoBillTransport IS NULL AND',' + ISNULL([BILL TRANS 1], '') + ',' + ISNULL([BILL TRANS 2], '') + ',' + ISNULL([BILL TRANS 3], '') + ',' + ISNULL([BILL TRANS 4], '') + ',' + ISNULL([BILL TRANS 5], '') + ',' LIKE '%,' + @StartWC + @NoBillTransport + @EndWC + ',%' THEN 1
WHEN 0 <
CASE WHEN @Complete IS NULL THEN 0
WHEN @Complete = 0 AND QUANTITÉ > ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
WHEN @Complete = 1 AND QUANTITÉ <= ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
ELSE 0
END
THEN 1
WHEN 0 <
CASE WHEN @bAnyDateReception = 0 THEN 0
WHEN date_de_reception1 BETWEENN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception2 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception3 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception4 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception5 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
ELSE 0
END
THEN 1
ELSE 0
END
)
THEN 1
ELSE 0
END
ELSE 0
END
ELSE --@ALLCriterias = 1 --retourner la ligne si TOUS les critÈres sont valides
CASE WHEN
(@NoCommande IS NULL OR E.[NO DE COMMANDE] LIKE @StartWC + CONVERT(VARCHAR(10), @NoCommande) + @EndWC )
AND (@NomFournisseur IS NULL OR E.FNOM LIKE @StartWC + @NomFournisseur + @EndWC )
AND (@bAnyDateCommande = 0 OR E.[DATE DE COMMNANDE] BETWEEN @DateCommandeD AND @DateCommandeF )
AND (@bAnyDateLivraison = 0 OR E.LIVRAISON BETWEEN @DateLivraisonD AND @DateLivraisonF )
AND 0 < CASE WHEN @bAnyPieceParamIsSet = 0 THEN 1
WHEN EXISTS (
SELECT
*
FROM dbo.DETAIL D
WHERE D.[NO DE COMMANDE] = E.[NO DE COMMANDE]
AND @bAnyParamIsSet = 1
AND 0 < CASE WHEN
(@NoContrat IS NULL OR D.[NO DE CONTRAT] LIKE @StartWC + @NoContrat + @EndWC )
AND (@PoClient IS NULL OR D.[PO CLIENT] LIKE @StartWC + @PoClient + @EndWC )
AND (@NomClient IS NULL OR D.[NOM DU CLIENT] LIKE @StartWC + @NomClient + @EndWC )
AND (@CodeDePiece IS NULL OR D.PNUM LIKE @StartWC + @CodeDePiece + @EndWC )
AND (@DescPiece IS NULL OR D.PNOM LIKE @StartWC + @DescPiece + @EndWC )
AND (@NoBonTravail IS NULL OR D.FT LIKE @StartWC + @NoBonTravail + @EndWC )
AND (@NoPackingSlip IS NULL OR ',' + ISNULL([PSLIP 1], '') + ',' + ISNULL([PSLIP 2], '') + ',' + ISNULL([PSLIP 3], '') + ',' + ISNULL([PSLIP 4], '') + ',' + ISNULL([PSLIP 5], '') + ',' LIKE '%,' + @StartWC + @NoPackingSlip + @EndWC + ',%' )
AND (@NoFacture IS NULL OR ',' + ISNULL([FACTURE 1], '') + ',' + ISNULL([FACTURE 2], '') + ',' + ISNULL([FACTURE 3], '') + ',' + ISNULL([FACTURE 4], '') + ',' + ISNULL([FACTURE 5], '') + ',' LIKE '%,' + @StartWC + @NoFacture + @EndWC + ',%' )
AND (@NoBillTransport IS NULL OR ',' + ISNULL([BILL TRANS 1], '') + ',' + ISNULL([BILL TRANS 2], '') + ',' + ISNULL([BILL TRANS 3], '') + ',' + ISNULL([BILL TRANS 4], '') + ',' + ISNULL([BILL TRANS 5], '') + ',' LIKE '%,' + @StartWC + @NoBillTransport + @EndWC + ',%' )
AND 0 <
CASE WHEN @Complete IS NULL THEN 1
WHEN @Complete = 0 AND QUANTITÉ > ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
WHEN @Complete = 1 AND QUANTITÉ <= ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
ELSE 0
END
AND 0 <
CASE WHEN @bAnyDateReception = 0 THEN 1
WHEN date_de_reception1 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception2 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception3 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception4 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception5 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
ELSE 0
END
THEN 1
ELSE 0
END
) THEN 1
ELSE 0
END
THEN 1
ELSE 0
END
END
ORDER BY
CASE WHEN @OrderBy = 'NoCommande' THEN E.[NO DE COMMANDE] ELSE NULL END
, CASE WHEN @OrderBy = 'NoContrat' THEN E.[NO DE CONTRAT] ELSE NULL END
, CASE WHEN @OrderBy = 'NomFournisseur' THEN E.FNOM ELSE NULL END
, CASE WHEN @OrderBy = 'NomClient' THEN E.[Nom liv] ELSE NULL END
, CASE WHEN @OrderBy = 'DateCommande' THEN E.[DATE DE COMMNANDE] ELSE NULL END
, CASE WHEN @OrderBy = 'Contact' THEN E.CONTACT ELSE NULL END
, CASE WHEN @OrderBy = 'DateLivraison' THEN E.LIVRAISON ELSE NULL END
, CASE WHEN @OrderBy = 'NoClient' THEN E.[Livre a] ELSE NULL END
SET NOCOUNT OFF
GO
-
I just got back from leave and Tech-Ed and noticed this
Very nice article.
There's nothing that gets at me more than a harsh or insulting reply to a post I've made, especially if it's one that took some work to do.
Especially if the harsh reply is from a frequent poster to the forums.
I'm surprised no one ever got insulted by my sig. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Don't worry, Gail... we considered the source
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply