Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mind Your Manners


Mind Your Manners

Author
Message
R Michael
R Michael
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 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
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20943 Visits: 9671

Can't make it work...

Care to post an exemple Robert?


R Michael
R Michael
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 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
R Michael
R Michael
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 275
My bad!

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

Sorry

-Robert

SQL guy and Houston Magician
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20943 Visits: 9671

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 .


R Michael
R Michael
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 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
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20943 Visits: 9671
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

R Michael
R Michael
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 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-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
R Michael
R Michael
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 275
I guess there is an online example here:http://www.simple-talk.com/prettifier/


SQL guy and Houston Magician
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20943 Visits: 9671
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search