"pmud" wrote:

> Hi,
>
> I an using the following stored procedure. Now what I want to do is:
> 1. If @month is null or nothing.. then @month's value should be all months,
> i.e it should  display data for all months.
>
> 2. If @ItemCode is not set to anything or is null, then it the stpored
> procedure should display data for that particular month but for all items.
>
> 3. It neither ItemCode or Month is anything, then it should display data for
> all months and all Item_Codes.
>
> How can this be done? Below is my stored procedure::
>
> CREATE PROCEDURE [dbo].[sp_POSDetails]
> (
> @manucode varchar(50),
> @itemCode varchar(50),
> @year smallint,
> @month smallint
> )
> AS
> SELECT DISTINCT
>                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
>                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> FROM         INVOICES INNER JOIN
>                       CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE INNER JOIN
>                       ITEMS INNER JOIN
>                       ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO INNER JOIN
>                       X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE ON
> INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> WHERE     (ITEMS.MANUCODE = @manucode)  AND  (ITEMS.ACTIVE='T') AND
> (X_INVOIC.STATUS = 9) AND (INVOICES.STATUS=9) AND (X_INVOIC.ITEM_CODE =
> @itemCode) AND (YEAR(INVOICES.ORDER_DATE) = @year)
>                       AND (MONTH(INVOICES.ORDER_DATE) = @month)
> GO
>
> --
> pmud

> X_INVOIC.ITEM_CODE = NULLIF(@itemCode,X_INVOIC.ITEM_CODE )
> AND YEAR(INVOICES.ORDER_DATE) = @year
> AND MONTH(INVOICES.ORDER_DATE) = NULLIF(@month,(MONTH,INVOICES.ORDER_DATE)

"John Bell" wrote:

> Hi
>
> Try something like:
>
> SELECT DISTINCT
>                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
>                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> FROM         INVOICES
> INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> INNER JOIN     ITEMS
> INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
>                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> WHERE   ITEMS.MANUCODE = @manucode
> AND  ITEMS.ACTIVE='T'
> AND X_INVOIC.STATUS = 9
> AND INVOICES.STATUS=9
> AND X_INVOIC.ITEM_CODE = ISNULL(@itemCode,X_INVOIC.ITEM_CODE )
> AND YEAR(INVOICES.ORDER_DATE) = @year
> AND MONTH(INVOICES.ORDER_DATE) = ISNULL(@month,(MONTH,INVOICES.ORDER_DATE)
>
> GO
>
> alternatively:
>
> SELECT DISTINCT
>                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
>                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> FROM         INVOICES
> INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> INNER JOIN     ITEMS
> INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
>                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> WHERE   ITEMS.MANUCODE = @manucode
> AND  ITEMS.ACTIVE='T'
> AND X_INVOIC.STATUS = 9
> AND INVOICES.STATUS=9
> AND ( X_INVOIC.ITEM_CODE = @itemCode OR @itemCode IS NULL)
> AND YEAR(INVOICES.ORDER_DATE) = @year
> AND ( MONTH(INVOICES.ORDER_DATE) = @month OR @month IS NULL )
>
> GO
>
> If you want to test add a check for an empty string is something like
> NULLIF(@month,'')
>
> John
>
> "pmud" wrote:
>
> > Hi,
> >
> > I an using the following stored procedure. Now what I want to do is:
> > 1. If @month is null or nothing.. then @month's value should be all months,
> > i.e it should  display data for all months.
> >
> > 2. If @ItemCode is not set to anything or is null, then it the stpored
> > procedure should display data for that particular month but for all items.
> >
> > 3. It neither ItemCode or Month is anything, then it should display data for
> > all months and all Item_Codes.
> >
> > How can this be done? Below is my stored procedure::
> >
> > CREATE PROCEDURE [dbo].[sp_POSDetails]
> > (
> > @manucode varchar(50),
> > @itemCode varchar(50),
> > @year smallint,
> > @month smallint
> > )
> > AS
> > SELECT DISTINCT
> >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > FROM         INVOICES INNER JOIN
> >                       CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE INNER JOIN
> >                       ITEMS INNER JOIN
> >                       ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO INNER JOIN
> >                       X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE ON
> > INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > WHERE     (ITEMS.MANUCODE = @manucode)  AND  (ITEMS.ACTIVE='T') AND
> > (X_INVOIC.STATUS = 9) AND (INVOICES.STATUS=9) AND (X_INVOIC.ITEM_CODE =
> > @itemCode) AND (YEAR(INVOICES.ORDER_DATE) = @year)
> >                       AND (MONTH(INVOICES.ORDER_DATE) = @month)
> > GO
> >
> > --
> > pmud

"pmud" wrote:

> Hi John,
>
> How can i add the NULLIF(Month,'') condition u mentioned? Will it be like
>  WHERE   ITEMS.MANUCODE = @manucode
> > X_INVOIC.ITEM_CODE = NULLIF(@itemCode,X_INVOIC.ITEM_CODE )
> > AND YEAR(INVOICES.ORDER_DATE) = @year
> > AND MONTH(INVOICES.ORDER_DATE) = NULLIF(@month,(MONTH,INVOICES.ORDER_DATE)
>
> --
> pmud
>
>
> "John Bell" wrote:
>
> > Hi
> >
> > Try something like:
> >
> > SELECT DISTINCT
> >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > FROM         INVOICES
> > INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> > INNER JOIN     ITEMS
> > INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> > INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
> >                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > WHERE   ITEMS.MANUCODE = @manucode
> > AND  ITEMS.ACTIVE='T'
> > AND X_INVOIC.STATUS = 9
> > AND INVOICES.STATUS=9
> > AND X_INVOIC.ITEM_CODE = ISNULL(@itemCode,X_INVOIC.ITEM_CODE )
> > AND YEAR(INVOICES.ORDER_DATE) = @year
> > AND MONTH(INVOICES.ORDER_DATE) = ISNULL(@month,(MONTH,INVOICES.ORDER_DATE)
> >
> > GO
> >
> > alternatively:
> >
> > SELECT DISTINCT
> >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > FROM         INVOICES
> > INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> > INNER JOIN     ITEMS
> > INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> > INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
> >                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > WHERE   ITEMS.MANUCODE = @manucode
> > AND  ITEMS.ACTIVE='T'
> > AND X_INVOIC.STATUS = 9
> > AND INVOICES.STATUS=9
> > AND ( X_INVOIC.ITEM_CODE = @itemCode OR @itemCode IS NULL)
> > AND YEAR(INVOICES.ORDER_DATE) = @year
> > AND ( MONTH(INVOICES.ORDER_DATE) = @month OR @month IS NULL )
> >
> > GO
> >
> > If you want to test add a check for an empty string is something like
> > NULLIF(@month,'')
> >
> > John
> >
> > "pmud" wrote:
> >
> > > Hi,
> > >
> > > I an using the following stored procedure. Now what I want to do is:
> > > 1. If @month is null or nothing.. then @month's value should be all months,
> > > i.e it should  display data for all months.
> > >
> > > 2. If @ItemCode is not set to anything or is null, then it the stpored
> > > procedure should display data for that particular month but for all items.
> > >
> > > 3. It neither ItemCode or Month is anything, then it should display data for
> > > all months and all Item_Codes.
> > >
> > > How can this be done? Below is my stored procedure::
> > >
> > > CREATE PROCEDURE [dbo].[sp_POSDetails]
> > > (
> > > @manucode varchar(50),
> > > @itemCode varchar(50),
> > > @year smallint,
> > > @month smallint
> > > )
> > > AS
> > > SELECT DISTINCT
> > >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> > >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > > FROM         INVOICES INNER JOIN
> > >                       CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE INNER JOIN
> > >                       ITEMS INNER JOIN
> > >                       ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO INNER JOIN
> > >                       X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE ON
> > > INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > > WHERE     (ITEMS.MANUCODE = @manucode)  AND  (ITEMS.ACTIVE='T') AND
> > > (X_INVOIC.STATUS = 9) AND (INVOICES.STATUS=9) AND (X_INVOIC.ITEM_CODE =
> > > @itemCode) AND (YEAR(INVOICES.ORDER_DATE) = @year)
> > >                       AND (MONTH(INVOICES.ORDER_DATE) = @month)
> > > GO
> > >
> > > --
> > > pmud

"John Bell" wrote:

> Hi
>
> For the first example it would be
>
> SELECT DISTINCT
>                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
>                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> FROM         INVOICES
> INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> INNER JOIN     ITEMS
> INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
>                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> WHERE   ITEMS.MANUCODE = @manucode
> AND  ITEMS.ACTIVE='T'
> AND X_INVOIC.STATUS = 9
> AND INVOICES.STATUS=9
> AND X_INVOIC.ITEM_CODE = ISNULL(NULLIF(@itemCode,''),X_INVOIC.ITEM_CODE )
> AND YEAR(INVOICES.ORDER_DATE) = @year
> AND MONTH(INVOICES.ORDER_DATE) =
> ISNULL(NULLIFF(@month,''),(MONTH,INVOICES.ORDER_DATE)
>
> GO
>
> John
>
> "pmud" wrote:
>
> > Hi John,
> >
> > How can i add the NULLIF(Month,'') condition u mentioned? Will it be like
> >  WHERE   ITEMS.MANUCODE = @manucode
> > > X_INVOIC.ITEM_CODE = NULLIF(@itemCode,X_INVOIC.ITEM_CODE )
> > > AND YEAR(INVOICES.ORDER_DATE) = @year
> > > AND MONTH(INVOICES.ORDER_DATE) = NULLIF(@month,(MONTH,INVOICES.ORDER_DATE)
> >
> > --
> > pmud
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Try something like:
> > >
> > > SELECT DISTINCT
> > >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> > >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > > FROM         INVOICES
> > > INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> > > INNER JOIN     ITEMS
> > > INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> > > INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
> > >                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > > WHERE   ITEMS.MANUCODE = @manucode
> > > AND  ITEMS.ACTIVE='T'
> > > AND X_INVOIC.STATUS = 9
> > > AND INVOICES.STATUS=9
> > > AND X_INVOIC.ITEM_CODE = ISNULL(@itemCode,X_INVOIC.ITEM_CODE )
> > > AND YEAR(INVOICES.ORDER_DATE) = @year
> > > AND MONTH(INVOICES.ORDER_DATE) = ISNULL(@month,(MONTH,INVOICES.ORDER_DATE)
> > >
> > > GO
> > >
> > > alternatively:
> > >
> > > SELECT DISTINCT
> > >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> > >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > > FROM         INVOICES
> > > INNER JOIN   CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE
> > > INNER JOIN     ITEMS
> > > INNER JOIN    ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO
> > > INNER JOIN    X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE
> > >                   ON INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > > WHERE   ITEMS.MANUCODE = @manucode
> > > AND  ITEMS.ACTIVE='T'
> > > AND X_INVOIC.STATUS = 9
> > > AND INVOICES.STATUS=9
> > > AND ( X_INVOIC.ITEM_CODE = @itemCode OR @itemCode IS NULL)
> > > AND YEAR(INVOICES.ORDER_DATE) = @year
> > > AND ( MONTH(INVOICES.ORDER_DATE) = @month OR @month IS NULL )
> > >
> > > GO
> > >
> > > If you want to test add a check for an empty string is something like
> > > NULLIF(@month,'')
> > >
> > > John
> > >
> > > "pmud" wrote:
> > >
> > > > Hi,
> > > >
> > > > I an using the following stored procedure. Now what I want to do is:
> > > > 1. If @month is null or nothing.. then @month's value should be all months,
> > > > i.e it should  display data for all months.
> > > >
> > > > 2. If @ItemCode is not set to anything or is null, then it the stpored
> > > > procedure should display data for that particular month but for all items.
> > > >
> > > > 3. It neither ItemCode or Month is anything, then it should display data for
> > > > all months and all Item_Codes.
> > > >
> > > > How can this be done? Below is my stored procedure::
> > > >
> > > > CREATE PROCEDURE [dbo].[sp_POSDetails]
> > > > (
> > > > @manucode varchar(50),
> > > > @itemCode varchar(50),
> > > > @year smallint,
> > > > @month smallint
> > > > )
> > > > AS
> > > > SELECT DISTINCT
> > > >                       X_INVOIC.ORDER_NO, X_INVOIC.STATUS, X_INVOIC.ITEM_QTY,
> > > > X_INVOIC.ITEM_PRICE, X_INVOIC.ITEM_CODE, INVOICES.ORDER_DATE,
> > > >                       ITEMS.DESCRIPT, CUST.NAME, CUST.CUST_CODE
> > > > FROM         INVOICES INNER JOIN
> > > >                       CUST ON INVOICES.CUST_CODE = CUST.CUST_CODE INNER JOIN
> > > >                       ITEMS INNER JOIN
> > > >                       ITEMHIST ON ITEMS.ITEMNO = ITEMHIST.ITEMNO INNER JOIN
> > > >                       X_INVOIC ON ITEMHIST.ITEMNO = X_INVOIC.ITEM_CODE ON
> > > > INVOICES.ORDER_NO = X_INVOIC.ORDER_NO
> > > > WHERE     (ITEMS.MANUCODE = @manucode)  AND  (ITEMS.ACTIVE='T') AND
> > > > (X_INVOIC.STATUS = 9) AND (INVOICES.STATUS=9) AND (X_INVOIC.ITEM_CODE =
> > > > @itemCode) AND (YEAR(INVOICES.ORDER_DATE) = @year)
> > > >                       AND (MONTH(INVOICES.ORDER_DATE) = @month)
> > > > GO
> > > >
> > > > --
> > > > pmud