Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Mind Your Manners Expand / Collapse
Author
Message
Posted Friday, October 20, 2006 10:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
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
Post #317027
Posted Friday, October 20, 2006 11:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610

Can't make it work...

 

Care to post an exemple Robert?

Post #317041
Posted Friday, October 20, 2006 11:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
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 a



It 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
Post #317047
Posted Friday, October 20, 2006 11:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
My bad!

The pre tag won't avoid the smileys. (but your code looks prettier!)

Sorry

-Robert



SQL guy and Houston Magician
Post #317048
Posted Friday, October 20, 2006 11:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610

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 .

Post #317050
Posted Friday, October 20, 2006 11:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
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
Post #317054
Posted Friday, October 20, 2006 11:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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 <
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 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 <
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
Post #317059
Posted Friday, October 20, 2006 12:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
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-
                
FROM #table 
                
WHERE b.effDate a.effDate AND a.currencycode  =  b.currencycodeIS NULL
     
THEN  (SELECT DATEADD(mmDATEDIFF(mm0GETDATE())+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
Post #317065
Posted Friday, October 20, 2006 12:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
I guess there is an online example here:http://www.simple-talk.com/prettifier/



SQL guy and Houston Magician
Post #317066
Posted Friday, October 20, 2006 12:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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 THEN '%' ELSE '' END

                               
@EndWC                 CASE WHEN @Location 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(@DateCommandeFAS BIT)

                               , 
@bAnyDateLivraison             CAST(ISDATE(@DateLivraisonD)   + ISDATE(@DateLivraisonFAS BIT)

                               , 
@bAnyDateReception             CAST(ISDATE(@DateReceptionD)   + ISDATE(@DateReceptionFAS BIT)



               
SET             @bAnyParamIsSet @bAnyEnteteParamIsSet @bAnyPieceParamIsSet



               
SELECT  

                                 
@DateCommandeD  ISNULL(@DateCommandeD0)

                               , 
@DateCommandeF ISNULL(@DateCommandeFDATEADD(D1GETDATE()))

                               , 
@DateLivraisonD        ISNULL(@DateLivraisonD0)

                               , 
@DateLivraisonF        ISNULL(@DateLivraisonFDATEADD(D1GETDATE()))

                               , 
@DateReceptionD        ISNULL(@DateReceptionD0)

                               , 
@DateReceptionF        ISNULL(@DateReceptionFDATEADD(D1GETDATE()))



       
SELECT    [NO DE COMMANDE]

                       
[NO DE CONTRAT]

                       
[Livre a]

                       
[Nom liv]

                       
FNOM

                       
[DATE DE COMMNANDE]

                       
LIVRAISON

                       
CONTACT

       
FROM            dbo.ENTETE E



       
WHERE   


                       
@bAnyParamIsSet AND            --allows sql server not to do any work if no param is set

                       


                       
CASE    WHEN @ALLCriterias 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 THEN

                                               
CASE    WHEN EXISTS      (

                                                                       
SELECT

                                                                                       
*

                                                                       
FROM            dbo.DETAIL D 

                                                                       
WHERE   D.[NO DE COMMANDE] E.[NO DE COMMANDE]

                                                                                       
AND @bAnyParamIsSet 1

                                                                                       
AND 

                                                                                       
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 

                                                                                                               
CASE    WHEN @Complete IS NULL THEN 0

                                                                                                                       
WHEN @Complete AND QUANTITÉ   > ([QTEE RECU 1] [QTEE RECU 2] [QTEE RECU 3] [QTEE RECU 4] [QTEE RECU 5]THEN 1

                                                                                                                       
WHEN @Complete 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 

                                                                                                               
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 

                                                       
ELSE 

                                               
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      OR E.[DATE DE COMMNANDE]       BETWEEN @DateCommandeD AND @DateCommandeF                          )

                                               AND     (
@bAnyDateLivraison     OR E.LIVRAISON                 BETWEEN @DateLivraisonD AND @DateLivraisonF                                )

                                               AND     
<      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 <   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     


                                                                                                                                               
CASE    WHEN @Complete IS NULL THEN 1

                                                                                                                                                       
WHEN @Complete AND QUANTITÉ   > ([QTEE RECU 1] [QTEE RECU 2] [QTEE RECU 3] [QTEE RECU 4] [QTEE RECU 5]THEN 1

                                                                                                                                                       
WHEN @Complete AND QUANTITÉ <= ([QTEE RECU 1] [QTEE RECU 2] [QTEE RECU 3] [QTEE RECU 4] [QTEE RECU 5]THEN 1

                                                                                                                                               
ELSE    0

                                                                                                                                               
END





                                                                                                                               
AND     

                                                                                                                                               
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 

                               
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
Post #317071
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse