/****** Object: Â StoredProcedure [dbo].[DBSP_GetNote] Â Â Script Date: 03/09/2011 17:49:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  Â
-- exec DBSP_GetNote null, null, null, null, null,'2011-03-08 12:19:41.417','2011-03-08 12:19:41.417'
--select * from dbo.TBL_Note Â
--exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'
CREATE PROCEDURE [dbo].[DBSP_GetNote] Â Â Â
( Â Â Â Â Â Â Â Â
 @NoteDescription varchar(200)='',        Â
 @NoteDefault bit,        Â
 @NoteStatus int,        Â
 @LastUpdatedBy int,
 @DivisionIDSearch int,   Â
 @LastUpdatedFrom Datetime = NULL,    Â
 @LastUpdatedTo Datetime = NULL
 Â
) Â Â Â Â Â Â Â Â
as        Â
begin        Â
declare @SQL varchar(8000) Â Â Â Â Â Â Â Â Â Â
       Â
Set @SQL = 'SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.NoteID) as DivisionName, s.StatusDescription, n.NoteDefault, Â Â Â
  u1.UserFirstName + ' + '''  ''' + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.CreatedDate,110) as CreatedDate,  Â
 u2.UserFirstName + ' + '''  ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.LastUpdatedDate,110) as LastUpdatedDate           Â
 FROM TBL_Note AS n          Â
 INNER JOIN TBL_eBid_Status s on n.StatusID = s.StatusID  Â
 INNER JOIN dbo.TBL_NoteDivision nd on n.NoteID = nd.NoteID   Â
 INNER JOIN TBL_Division d on nd.DivisionID = d.DivisionID       Â
 LEFT JOIN TBL_eBid_User u1 on n.CreatedBy = u1.UserID      Â
 LEFT JOIN TBL_eBid_User u2 on n.LastUpdatedBy = u2.UserID          Â
 Where n.NoteID > 0 '        Â
       Â
if(@NoteDescription is not null and @NoteDescription <>Â '') Â Â Â Â Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.NoteDescription LIKE ''%'+@NoteDescription+'%'''        Â
 End        Â
       Â
if(@NoteDefault = 1) Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.NoteDefault =  '+ convert(varchar(5), @NoteDefault)        Â
 End        Â
       Â
if(@NoteStatus >Â 0) Â Â Â Â Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.StatusID =  '+ convert(varchar(5),@NoteStatus )        Â
 End        Â
       Â
if(@LastUpdatedBy >Â 0) Â Â Â Â Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.LastUpdatedBy =  ' + convert(varchar(5),@LastUpdatedBY )        Â
 End  Â
IF(@DivisionIDSearch <>Â 0) Â Â Â Â
BEGIN Â Â Â Â Â
  SET @SQL = @SQL + ' and d.DivisionID = '+convert(varchar(5), @DivisionIDSearch)     Â
END Â Â Â
    Â
 --If @LastUpdatedFrom Is Not Null    Â
 --Begin     Â
 -- Set @SQL = @SQL + ' and n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''     Â
 --End    Â
    Â
 --If @LastUpdatedTo Is Not Null     Â
 --Begin     Â
 -- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''     Â
 --End Â
Â
 --End          Â
Â
Â
 --IF (@LastUpdatedFrom IS NOT NULL AND  @LastUpdatedTo  IS NOT NULL)
      --                 BEGIN
      --                            IF (@LastUpdatedFrom = @LastUpdatedTo )
      --                                  BEGIN
          Â
      --                                       SET @SQL=@SQL + ' AND  n.LastUpdatedDate ='''+convert(varchar(30),@LastUpdatedFrom,102)+''''
      --                                  END
      --                            ELSE
      --                                  BEGIN
      --                                       SET  @SQL =  @SQL + 'AND  n.LastUpdatedDate  BETWEEN ' + '''' +  +convert(varchar(30),@LastUpdatedFrom,102)+''''  + '''' + ' AND ' + '''' +  +convert(varchar(30),@LastUpdatedFrom,102)+''''
      --                           END           Â
      --                 END                  Â
 Â
  IF (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
                            --BEGIN
                           Â
                                 --IF (@LastUpdatedFrom IS NOT NULL )
                                       BEGIN
                                            SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''
                                       END
                                 ELSE
                                       BEGIN
                                            SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+'''' Â
                                Â
                                 END
                                Â
                                Â
                            END      Â
 Â
  --end
print @SQL Â Â Â Â Â Â Â Â
exec (@SQL) Â
 Â
 Â
  --END
you haven't got the date range coding active in the procedure you've given us...
.
Ephraim Wangoya
You need to add time to your @LastUpdatedTo variable to reflect the end of the day
'03/04/2011 23:59:59'
IF (@LastUpdatedFrom IS NOT NULL and @LastUpdatedTo IS NOT NULL) BEGIN SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+'''' END ELSE BEGIN set @LastUpdatedFrom = DATEADD(SECOND, -1, DATEADD(DD, 1, @LastUpdatedFrom)) SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+'''' END
IF (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
 BEGIN
  SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''
 END
 ELSE
 BEGIN
  set @LastUpdatedTo= DATEADD(SECOND, -1, DATEADD(DD, 1, @LastUpdatedTo))
  SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+'''' Â
                 Â
 END
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
sqlcurious
ASKER
sorry didnot help
--exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'
ALTER PROCEDURE [dbo].[DBSP_GetNote] Â Â Â
( Â Â Â Â Â Â Â Â
 @NoteDescription varchar(200)='',        Â
 @NoteDefault bit,        Â
 @NoteStatus int,        Â
 @LastUpdatedBy int,
 @DivisionIDSearch int,   Â
 @LastUpdatedFrom Datetime = NULL,    Â
 @LastUpdatedTo Datetime = NULL
 Â
) Â Â Â Â Â Â Â Â
as        Â
begin        Â
declare @SQL varchar(8000) Â Â Â Â Â Â Â Â Â Â
       Â
Set @SQL = 'SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.NoteID) as DivisionName, s.StatusDescription, n.NoteDefault, Â Â Â
  u1.UserFirstName + ' + '''  ''' + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.CreatedDate,110) as CreatedDate,  Â
 u2.UserFirstName + ' + '''  ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.LastUpdatedDate,110) as LastUpdatedDate           Â
 FROM TBL_Note AS n          Â
 INNER JOIN TBL_eBid_Status s on n.StatusID = s.StatusID  Â
 INNER JOIN dbo.TBL_NoteDivision nd on n.NoteID = nd.NoteID   Â
 INNER JOIN TBL_Division d on nd.DivisionID = d.DivisionID       Â
 LEFT JOIN TBL_eBid_User u1 on n.CreatedBy = u1.UserID      Â
 LEFT JOIN TBL_eBid_User u2 on n.LastUpdatedBy = u2.UserID          Â
 Where n.NoteID > 0 '        Â
       Â
if(@NoteDescription is not null and @NoteDescription <>Â '') Â Â Â Â Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.NoteDescription LIKE ''%'+@NoteDescription+'%'''        Â
 End        Â
       Â
if(@NoteDefault = 1) Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.NoteDefault =  '+ convert(varchar(5), @NoteDefault)        Â
 End        Â
       Â
if(@NoteStatus >Â 0) Â Â Â Â Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.StatusID =  '+ convert(varchar(5),@NoteStatus )        Â
 End        Â
       Â
if(@LastUpdatedBy >Â 0) Â Â Â Â Â Â Â Â
 Begin        Â
  Set @SQL =  @SQL + ' and n.LastUpdatedBy =  ' + convert(varchar(5),@LastUpdatedBY )        Â
 End  Â
IF(@DivisionIDSearch <>Â 0) Â Â Â Â
BEGIN Â Â Â Â Â
  SET @SQL = @SQL + ' and d.DivisionID = '+convert(varchar(5), @DivisionIDSearch)     Â
END Â Â Â
    Â
 --If @LastUpdatedFrom Is Not Null    Â
 --Begin     Â
 -- Set @SQL = @SQL + ' and n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''     Â
 --End    Â
    Â
 --If @LastUpdatedTo Is Not Null     Â
 --Begin     Â
 -- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''     Â
 --End Â
Â
 --End          Â
Â
Â
 --IF (@LastUpdatedFrom IS NOT NULL AND  @LastUpdatedTo  IS NOT NULL)
      --                 BEGIN
      --                            IF (@LastUpdatedFrom = @LastUpdatedTo )
      --                                  BEGIN
          Â
      --                                       SET @SQL=@SQL + ' AND  n.LastUpdatedDate ='''+convert(varchar(30),@LastUpdatedFrom,102)+''''
      --                                  END
      --                            ELSE
      --                                  BEGIN
      --                                       SET  @SQL =  @SQL + 'AND  n.LastUpdatedDate  BETWEEN ' + '''' +  +convert(varchar(30),@LastUpdatedFrom,102)+''''  + '''' + ' AND ' + '''' +  +convert(varchar(30),@LastUpdatedFrom,102)+''''
      --                           END           Â
      --                 END                  Â
 Â
  IF (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
 BEGIN
  SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''
 END
 ELSE
 BEGIN
  set @LastUpdatedTo= DATEADD(SECOND, -1, DATEADD(DD, 1, @LastUpdatedTo))
  SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+'''' Â
 end                 Â
 END
 Â
  --end
print @SQL Â Â Â Â Â Â Â Â
exec (@SQL) Â
 Â
 Â
  --END
 Â
 Â
 Â
  when i execute this :exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'
it  gave the following result
CREATE PROCEDURE [dbo].[DBSP_GETNOTE]( @NoteDescription VARCHAR(200) = '', @NoteDefault BIT, @NoteStatus INT, @LastUpdatedBy INT, @DivisionIDSearch INT, @LastUpdatedFrom DATETIME = NULL, @LastUpdatedTo DATETIME = NULL) AS BEGIN DECLARE @SQL VARCHAR(8000) SET @SQL = 'SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.NoteID) as DivisionName, s.StatusDescription, n.NoteDefault, u1.UserFirstName + ' + ''' ''' + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.CreatedDate,110) as CreatedDate, u2.UserFirstName + ' + ''' ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.LastUpdatedDate,110) as LastUpdatedDate FROM TBL_Note AS n INNER JOIN TBL_eBid_Status s on n.StatusID = s.StatusID INNER JOIN dbo.TBL_NoteDivision nd on n.NoteID = nd.NoteID INNER JOIN TBL_Division d on nd.DivisionID = d.DivisionID LEFT JOIN TBL_eBid_User u1 on n.CreatedBy = u1.UserID LEFT JOIN TBL_eBid_User u2 on n.LastUpdatedBy = u2.UserID Where n.NoteID > 0 ' IF (@NoteDescription IS NOT NULL AND @NoteDescription <> '') BEGIN SET @SQL = @SQL + ' and n.NoteDescription LIKE ''%' + @NoteDescription + '%''' END IF (@NoteDefault = 1) BEGIN SET @SQL = @SQL + ' and n.NoteDefault = ' + CONVERT(VARCHAR(5),@NoteDefault) END IF (@NoteStatus > 0) BEGIN SET @SQL = @SQL + ' and n.StatusID = ' + CONVERT(VARCHAR(5),@NoteStatus) END IF (@LastUpdatedBy > 0) BEGIN SET @SQL = @SQL + ' and n.LastUpdatedBy = ' + CONVERT(VARCHAR(5),@LastUpdatedBY) END IF (@DivisionIDSearch <> 0) BEGIN SET @SQL = @SQL + ' and d.DivisionID = ' + CONVERT(VARCHAR(5),@DivisionIDSearch) END --If @LastUpdatedFrom Is Not Null --Begin -- Set @SQL = @SQL + ' and n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+'''' --End --If @LastUpdatedTo Is Not Null --Begin -- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+'''' --End --End --IF (@LastUpdatedFrom IS NOT NULL AND @LastUpdatedTo IS NOT NULL) -- BEGIN -- IF (@LastUpdatedFrom = @LastUpdatedTo ) -- BEGIN -- SET @SQL=@SQL + ' AND n.LastUpdatedDate ='''+convert(varchar(30),@LastUpdatedFrom,102)+'''' -- END -- ELSE -- BEGIN -- SET @SQL = @SQL + 'AND n.LastUpdatedDate BETWEEN ' + '''' + +convert(varchar(30),@LastUpdatedFrom,102)+'''' + '''' + ' AND ' + '''' + +convert(varchar(30),@LastUpdatedFrom,102)+'''' -- END -- END IF (@LastUpdatedFrom IS NOT NULL AND @LastUpdatedTo IS NOT NULL) --BEGIN --IF (@LastUpdatedFrom IS NOT NULL ) BEGIN SET @SQL = @SQL + ' AND CONVERT(DATE,n.LastUpdatedDate) >=''' + CONVERT(VARCHAR,CONVERT(DATE,@LastUpdatedFrom)) + '''' END ELSE BEGIN SET @SQL = @SQL + ' AND CONVERT(DATE,n.LastUpdatedDate) <= ''' + CONVERT(VARCHAR,CONVERT(DATE,@LastUpdatedTo)) + '''' END END --end PRINT @SQL EXEC( @SQL) --END GO
but consider also that you need to probably include a time component on the from and to datetimes...
otherwise with the "same" date you are only looking at the data created at midnight on thaty day...
create PROCEDURE [dbo].[DBSP_GetNote] ( @NoteDescription varchar(200)='', @NoteDefault bit, @NoteStatus int, @LastUpdatedBy int, @DivisionIDSearch int, @LastUpdatedFrom Datetime = NULL, @LastUpdatedTo Datetime = NULL) as begin declare @SQL varchar(8000) ,@fromdate varchar(30),@todate varchar(30) Select @fromdate = CONVERT(char(9),@lastupdatedfrom,112)+CONVERT(char(8),@lastupdatedfrom,108) ,@todate = CONVERT(char(9),@lastupdatedto,112)+CONVERT(char(8),@lastupdatedto,108)Select @SQL = 'SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.NoteID) as DivisionName,' +'s.StatusDescription, n.NoteDefault, u1.UserFirstName + ' + ''' ''' + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.CreatedDate,110) as CreatedDate,' + ' u2.UserFirstName + ' + ''' ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.LastUpdatedDate,110) as LastUpdatedDate' + ' FROM TBL_Note AS n ' +' INNER JOIN TBL_eBid_Status s on n.StatusID = s.StatusID' +' INNER JOIN dbo.TBL_NoteDivision nd on n.NoteID = nd.NoteID' +' INNER JOIN TBL_Division d on nd.DivisionID = d.DivisionID' +' LEFT JOIN TBL_eBid_User u1 on n.CreatedBy = u1.UserID' +' LEFT JOIN TBL_eBid_User u2 on n.LastUpdatedBy = u2.UserID' +' Where n.NoteID > 0 ' +case when (@NoteDescription is not null and @NoteDescription <> '') then ' and n.NoteDescription LIKE ''%'+@NoteDescription+'%''' else '' End +case when (@NoteDefault = 1) then ' and n.NoteDefault = '+ convert(varchar(5), @NoteDefault) else '' End +case when (@NoteStatus > 0) then ' and n.StatusID = '+ convert(varchar(5),@NoteStatus ) else '' End +case when (@LastUpdatedBy > 0) then ' and n.LastUpdatedBy = ' + convert(varchar(5),@LastUpdatedBY ) else '' End +case when (@DivisionIDSearch <> 0) then ' and d.DivisionID = '+convert(varchar(5), @DivisionIDSearch) else ''END +case when (@LastUpdatedFrom IS NULL and @LastUpdatedTo IS NULL) then '' else ' and n.lastupdateddate ' + case when (@LastUpdatedFrom IS NOT NULL and @LastUpdatedTo IS NOT NULL) then 'between '''+@fromdate+''' and '''+@todate When @lastupdatedto IS not null then '<= '''+@todate else '>= '''+@fromdate end + '''' end print @SQL exec(@SQL) End Return GO
Unlimited question asking, solutions, articles and more.
sqlcurious
ASKER
i got error
Msg 243, Level 16, State 1, Procedure DBSP_GETNOTE, Line 182
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Procedure DBSP_GETNOTE, Line 186
Type DATE is not a defined system type.
Lowfatspread
confirm the edition and version of sql server you are using
and also the compatability level of the database ....
date is a valid datatype for sql 2008 database not running at a downgraded compatabilty level.
also confirm what the datatypes are for your lastupdateddate column
sqlcurious
ASKER
Msg 241, Level 16, State 1, Procedure DBSP_GetNote, Line 22
Conversion failed when converting datetime from character string.
The datatype for lastupdated date column is datetime
i did select @@version and it says
Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)  Mar  3 2007 18:40:02  Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I am connecting to my remote server and the db is in  2005 sql server
Your help has saved me hundreds of hours of internet surfing.
fblack61
sqlcurious
ASKER
now it didnot show any error but i did not return any rows
Lowfatspread
you have the question in the wrong zone then it should be a sql server 2005 zone.
can you please post the display sql , and your invcation parameters...
sqlcurious
ASKER
even though it is in sql 2005 i think it doesnot matter much in case of  datatype.Can u please check in the SP why it is not returning any rows?
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
you haven't got the date range coding active in the procedure you've given us...
.