Dynamic Where Clause using COALESCE() question

microsoft.public.sqlserver.programming

11 Aug 2006 6:26 PM

glen

I have a stored proc with 6 input paramaters, 4 of which I want to build the
where clause. The first 2 work fine, the second 2 when used cause an empty
recodset to be returned. There are no NULL values in any of the fields
concerned. I'm at a loss, I'm also sure it's something basic as this is my
first use of the COALECSE function in other than the academic setting. Please
help in my search for a clue.....TIA.

Code Follows:
ALTER PROCEDURE [ADMIN\n002262].GET_COLLEAGUE_LETTERS_PAGED_SP
@searchText varchar(50) = NULL,
@regionID INT = 0, @Year int = 0,
@Month int = 0, @Page int, @RecsPerPage int

AS

SET NOCOUNT ON
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
DECLARE @Pager TABLE
    (
    rID INT IDENTITY,
    DOCUMENT_ID INT,
    DOCUMENT_ARCHIVED_IND BIT,
    AUTHOR_FIRST_NM VARCHAR(50),
    AUTHOR_LAST_NM VARCHAR(50),
    DOCUMENT_TITLE_TXT VARCHAR(300),
    DOCUMENT_FILE_PATH_TXT VARCHAR(50),
    DOCUMENT_KEYWORD_TXT VARCHAR(500),
    REGION_ID INT,
    CREATE_BY_DT DATETIME,
    pYear int,
    pMonth int,
    regionName VARCHAR(100)
    )

DECLARE @vars int
SET @vars = @regionID + @Year + @Month
SET @searchText = LTRIM(RTRIM(@searchText))

IF @searchText IS NOT NULL OR @vars > 0
    BEGIN
        INSERT INTO @Pager         (DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,DOCUMENT_TITLE_TXT,         DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,pYear,pMonth,regionName)
        SELECT DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,         DOCUMENT_TITLE_TXT,DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,
        YEAR(CREATE_BY_DT),MONTH(CREATE_BY_DT),regionName FROM DEAR_COLLEAGUE LEFT
JOIN tblRegion ON REGION_ID = pk_regionID
        WHERE REGION_ID = COALESCE(@regionID,REGION_ID)
        /*
            Troubleshooting this section
        AND YEAR(CREATE_BY_DT) = COALESCE(@Year,YEAR(CREATE_BY_DT))
        AND MONTH(CREATE_BY_DT) = COALESCE(@Month,MONTH(CREATE_BY_DT))
        */
        AND (DOCUMENT_TITLE_TXT LIKE COALESCE('%' + @searchText +
'%',DOCUMENT_TITLE_TXT)
        OR DOCUMENT_KEYWORD_TXT LIKE COALESCE('%' + @searchText +
'%',DOCUMENT_KEYWORD_TXT))

        SELECT *,
               MoreRecords =
            (
            SELECT COUNT(*)
            FROM @Pager TI
            WHERE TI.rID >= @LastRec
            )
        FROM @Pager WHERE
        rID > @FirstRec AND rID < @LastRec
        ORDER BY DOCUMENT_ID DESC

    END
ELSE
    BEGIN
        INSERT INTO @Pager         (DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,DOCUMENT_TITLE_TXT,         DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,regionName)
        SELECT DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,         DOCUMENT_TITLE_TXT,DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,
        regionName FROM DEAR_COLLEAGUE LEFT JOIN tblRegion ON REGION_ID =
pk_regionID

        SELECT *,
               MoreRecords =
            (
            SELECT COUNT(*)
            FROM @Pager TI
            WHERE TI.rID >= @LastRec
            )
        FROM @Pager
        WHERE rID > @FirstRec AND rID < @LastRec ORDER BY DOCUMENT_ID DESC
    END

SET NOCOUNT OFF

--
glen

11 Aug 2006 7:32 PM

Mike C#

You have @RegionID, @Year and @Month set to default to 0. So if you don't
include the @RegionID, @Year and @Month parameters, your statements are
looking like this (replacing variables with actual values):

WHERE REGION_ID = COALESCE(0,REGION_ID)
AND YEAR(CREATE_BY_DT) = COALESCE(0,YEAR(CREATE_BY_DT))
AND MONTH(CREATE_BY_DT) = COALESCE(0,MONTH(CREATE_BY_DT))

Which translates to this:

WHERE REGION_ID = 0
AND YEAR(CREATE_BY_DT) = 0
AND MONTH(CREATE_BY_DT) = 0

Which is never going to be true (at least not with any calendars I've seen).
Try changing your defaults in the definition:

ALTER PROCEDURE [ADMIN\n002262].GET_COLLEAGUE_LETTERS_PAGED_SP
@searchText VARCHAR(50) = NULL,
@regionID INT = NULL, -- changed default to NULL
@Year INT = NULL, -- changed default to NULL
@Month INT = NULL, -- changed default to NULL
@Page INT,
@RecsPerPage INT
AS

That should make your COALESCE functions work properly for you when you
leave out the @Year, @Month or @regionID parameters.

Show quote

"glen" <g***@discussions.microsoft.com> wrote in message
news:3260A5DD-F78D-4D90-9366-0373288E3327@microsoft.com...
>I have a stored proc with 6 input paramaters, 4 of which I want to build
>the
> where clause. The first 2 work fine, the second 2 when used cause an empty
> recodset to be returned. There are no NULL values in any of the fields
> concerned. I'm at a loss, I'm also sure it's something basic as this is my
> first use of the COALECSE function in other than the academic setting.
> Please
> help in my search for a clue.....TIA.
>
> Code Follows:
> ALTER PROCEDURE [ADMIN\n002262].GET_COLLEAGUE_LETTERS_PAGED_SP
> @searchText varchar(50) = NULL,
> @regionID INT = 0, @Year int = 0,
> @Month int = 0, @Page int, @RecsPerPage int
>
> AS
>
> SET NOCOUNT ON
> DECLARE @FirstRec int, @LastRec int
> SELECT @FirstRec = (@Page - 1) * @RecsPerPage
> SELECT @LastRec = (@Page * @RecsPerPage + 1)
> DECLARE @Pager TABLE
> (
> rID INT IDENTITY,
> DOCUMENT_ID INT,
> DOCUMENT_ARCHIVED_IND BIT,
> AUTHOR_FIRST_NM VARCHAR(50),
> AUTHOR_LAST_NM VARCHAR(50),
> DOCUMENT_TITLE_TXT VARCHAR(300),
> DOCUMENT_FILE_PATH_TXT VARCHAR(50),
> DOCUMENT_KEYWORD_TXT VARCHAR(500),
> REGION_ID INT,
> CREATE_BY_DT DATETIME,
> pYear int,
> pMonth int,
> regionName VARCHAR(100)
> )
>
> DECLARE @vars int
> SET @vars = @regionID + @Year + @Month
> SET @searchText = LTRIM(RTRIM(@searchText))
>
> IF @searchText IS NOT NULL OR @vars > 0
> BEGIN
> INSERT INTO @Pager
> (DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,DOCUMENT_TITLE_TXT,
> DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,pYear,pMonth,regionName)
> SELECT DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,
> DOCUMENT_TITLE_TXT,DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,
> YEAR(CREATE_BY_DT),MONTH(CREATE_BY_DT),regionName FROM DEAR_COLLEAGUE LEFT
> JOIN tblRegion ON REGION_ID = pk_regionID
> WHERE REGION_ID = COALESCE(@regionID,REGION_ID)
> /*
> Troubleshooting this section
> AND YEAR(CREATE_BY_DT) = COALESCE(@Year,YEAR(CREATE_BY_DT))
> AND MONTH(CREATE_BY_DT) = COALESCE(@Month,MONTH(CREATE_BY_DT))
> */
> AND (DOCUMENT_TITLE_TXT LIKE COALESCE('%' + @searchText +
> '%',DOCUMENT_TITLE_TXT)
> OR DOCUMENT_KEYWORD_TXT LIKE COALESCE('%' + @searchText +
> '%',DOCUMENT_KEYWORD_TXT))
>
> SELECT *,
> MoreRecords =
> (
> SELECT COUNT(*)
> FROM @Pager TI
> WHERE TI.rID >= @LastRec
> )
> FROM @Pager WHERE
> rID > @FirstRec AND rID < @LastRec
> ORDER BY DOCUMENT_ID DESC
>
> END
> ELSE
> BEGIN
> INSERT INTO @Pager
> (DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,DOCUMENT_TITLE_TXT,
> DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,regionName)
> SELECT DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,
> DOCUMENT_TITLE_TXT,DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,
> regionName FROM DEAR_COLLEAGUE LEFT JOIN tblRegion ON REGION_ID =
> pk_regionID
>
> SELECT *,
> MoreRecords =
> (
> SELECT COUNT(*)
> FROM @Pager TI
> WHERE TI.rID >= @LastRec
> )
> FROM @Pager
> WHERE rID > @FirstRec AND rID < @LastRec ORDER BY DOCUMENT_ID DESC
> END
>
> SET NOCOUNT OFF
>
> --
> glen

11 Aug 2006 7:41 PM

Aaron Bertrand [SQL Server MVP]

> You have @RegionID, @Year and @Month set to default to 0. So if you don't
> include the @RegionID, @Year and @Month parameters, your statements are
> looking like this (replacing variables with actual values):

On the other hand, it may be the case that the application is explicitly
specifying the NULL keyword to those parameters?

The reason I say that is it sounds like @searchText and @regionID are
working as expected.

A

11 Aug 2006 8:10 PM

glen

That got it. Many thanks.

--
glen

Show quote

"Mike C#" wrote:

> You have @RegionID, @Year and @Month set to default to 0. So if you don't
> include the @RegionID, @Year and @Month parameters, your statements are
> looking like this (replacing variables with actual values):
>
> WHERE REGION_ID = COALESCE(0,REGION_ID)
> AND YEAR(CREATE_BY_DT) = COALESCE(0,YEAR(CREATE_BY_DT))
> AND MONTH(CREATE_BY_DT) = COALESCE(0,MONTH(CREATE_BY_DT))
>
> Which translates to this:
>
> WHERE REGION_ID = 0
> AND YEAR(CREATE_BY_DT) = 0
> AND MONTH(CREATE_BY_DT) = 0
>
> Which is never going to be true (at least not with any calendars I've seen).
> Try changing your defaults in the definition:
>
> ALTER PROCEDURE [ADMIN\n002262].GET_COLLEAGUE_LETTERS_PAGED_SP
> @searchText VARCHAR(50) = NULL,
> @regionID INT = NULL, -- changed default to NULL
> @Year INT = NULL, -- changed default to NULL
> @Month INT = NULL, -- changed default to NULL
> @Page INT,
> @RecsPerPage INT
> AS
>
> That should make your COALESCE functions work properly for you when you
> leave out the @Year, @Month or @regionID parameters.
>
> "glen" <g***@discussions.microsoft.com> wrote in message
> news:3260A5DD-F78D-4D90-9366-0373288E3327@microsoft.com...
> >I have a stored proc with 6 input paramaters, 4 of which I want to build
> >the
> > where clause. The first 2 work fine, the second 2 when used cause an empty
> > recodset to be returned. There are no NULL values in any of the fields
> > concerned. I'm at a loss, I'm also sure it's something basic as this is my
> > first use of the COALECSE function in other than the academic setting.
> > Please
> > help in my search for a clue.....TIA.
> >
> > Code Follows:
> > ALTER PROCEDURE [ADMIN\n002262].GET_COLLEAGUE_LETTERS_PAGED_SP
> > @searchText varchar(50) = NULL,
> > @regionID INT = 0, @Year int = 0,
> > @Month int = 0, @Page int, @RecsPerPage int
> >
> > AS
> >
> > SET NOCOUNT ON
> > DECLARE @FirstRec int, @LastRec int
> > SELECT @FirstRec = (@Page - 1) * @RecsPerPage
> > SELECT @LastRec = (@Page * @RecsPerPage + 1)
> > DECLARE @Pager TABLE
> > (
> > rID INT IDENTITY,
> > DOCUMENT_ID INT,
> > DOCUMENT_ARCHIVED_IND BIT,
> > AUTHOR_FIRST_NM VARCHAR(50),
> > AUTHOR_LAST_NM VARCHAR(50),
> > DOCUMENT_TITLE_TXT VARCHAR(300),
> > DOCUMENT_FILE_PATH_TXT VARCHAR(50),
> > DOCUMENT_KEYWORD_TXT VARCHAR(500),
> > REGION_ID INT,
> > CREATE_BY_DT DATETIME,
> > pYear int,
> > pMonth int,
> > regionName VARCHAR(100)
> > )
> >
> > DECLARE @vars int
> > SET @vars = @regionID + @Year + @Month
> > SET @searchText = LTRIM(RTRIM(@searchText))
> >
> > IF @searchText IS NOT NULL OR @vars > 0
> > BEGIN
> > INSERT INTO @Pager
> > (DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,DOCUMENT_TITLE_TXT,
> > DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,pYear,pMonth,regionName)
> > SELECT DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,
> > DOCUMENT_TITLE_TXT,DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,
> > YEAR(CREATE_BY_DT),MONTH(CREATE_BY_DT),regionName FROM DEAR_COLLEAGUE LEFT
> > JOIN tblRegion ON REGION_ID = pk_regionID
> > WHERE REGION_ID = COALESCE(@regionID,REGION_ID)
> > /*
> > Troubleshooting this section
> > AND YEAR(CREATE_BY_DT) = COALESCE(@Year,YEAR(CREATE_BY_DT))
> > AND MONTH(CREATE_BY_DT) = COALESCE(@Month,MONTH(CREATE_BY_DT))
> > */
> > AND (DOCUMENT_TITLE_TXT LIKE COALESCE('%' + @searchText +
> > '%',DOCUMENT_TITLE_TXT)
> > OR DOCUMENT_KEYWORD_TXT LIKE COALESCE('%' + @searchText +
> > '%',DOCUMENT_KEYWORD_TXT))
> >
> > SELECT *,
> > MoreRecords =
> > (
> > SELECT COUNT(*)
> > FROM @Pager TI
> > WHERE TI.rID >= @LastRec
> > )
> > FROM @Pager WHERE
> > rID > @FirstRec AND rID < @LastRec
> > ORDER BY DOCUMENT_ID DESC
> >
> > END
> > ELSE
> > BEGIN
> > INSERT INTO @Pager
> > (DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,DOCUMENT_TITLE_TXT,
> > DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,regionName)
> > SELECT DOCUMENT_ID,DOCUMENT_ARCHIVED_IND,AUTHOR_FIRST_NM,AUTHOR_LAST_NM,
> > DOCUMENT_TITLE_TXT,DOCUMENT_FILE_PATH_TXT,DOCUMENT_KEYWORD_TXT,REGION_ID,CREATE_BY_DT,
> > regionName FROM DEAR_COLLEAGUE LEFT JOIN tblRegion ON REGION_ID =
> > pk_regionID
> >
> > SELECT *,
> > MoreRecords =
> > (
> > SELECT COUNT(*)
> > FROM @Pager TI
> > WHERE TI.rID >= @LastRec
> > )
> > FROM @Pager
> > WHERE rID > @FirstRec AND rID < @LastRec ORDER BY DOCUMENT_ID DESC
> > END
> >
> > SET NOCOUNT OFF
> >
> > --
> > glen
>
>
>