Link to home
Start Free TrialLog in
Avatar of sqlcurious
sqlcuriousFlag for United States of America

asked on

help in procedure

stored proc help
i have a problem in stored proc the date range is not working if i execute following  in the procedure.NO rows are selected

exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'


USE [eBidManager]
GO

/****** 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

   

   
   

GO
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Try this:

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,121)+''''          
 End        
         
 If @LastUpdatedTo Is Not Null          
 Begin          
  Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,121)+''''          
 End  

print @SQL                
exec (@SQL)  
   
END

Open in new window

Try passing your date in this format 'yyyy-dd-mm'
Avatar of sqlcurious

ASKER

it did not return any rows
Avatar of Sharath S
Try this.
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(30),@LastUpdatedFrom,101) + ''''
      END 
    ELSE 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(DATE,n.LastUpdatedDate) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,101) + ''''
      END 
  END 

--end 
PRINT @SQL 

EXEC( @SQL) 

--END 
GO

Open in new window

First the error was date is not the system datatype then i changed to datetime
and executed the statement
exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011' and got following result
for lastupdateddate  column which is not desired
03-04-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
Check this.
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(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
      END 
    ELSE 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
  END 

--end 
PRINT @SQL 

EXEC( @SQL) 

--END 
GO

Open in new window

Its not working
when i execute this

exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'  it showed following result.i just took the column name.


LastUpdatedDate
03-04-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
Can you post the SQL with your PRINT statement?
i didnot get u .........there is a print statement in the sql.If u are asking anything else please let me know
Alter your SP like this and run the SP. Then post the statement.
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 
      --IF (@LastUpdatedFrom IS NOT NULL ) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
      END 
    ELSE 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
  END 

--end 
PRINT @SQL 

--EXEC( @SQL) 

--END 
GO

Open in new window

i executed following
exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'
and got the result


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  AND CONVERT(varchar,n.LastUpdatedDate,112) >='20110308'
Thats strange. Did you really pass 03/04/2011. If so, how did you get 20110308 in your query?
thats tru if i exceutw following
 
exec DBSP_GetNote '','',null,null,'','03/08/2011','03/08/2011'.I get following

LastUpdatedDate
03-08-2011
03-09-2011
03-09-2011
03-09-2011
Run this.
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 
      --IF (@LastUpdatedFrom IS NOT NULL ) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
    /*ELSE 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END */
  END 

--end 
PRINT @SQL 

EXEC( @SQL) 

--END 
GO

Open in new window


It gave the result partially.When i execute      exec DBSP_GetNote '','',null,null,'','03/08/2011','03/08/2011'.It showed following row only
LastUpdatedDate
03-08-2011

I am taking the data from the table 'tbl_note'  and when i exceute select * from tbl_note  i found following data for the date 3/08/11 along with the time.I think time is playing the role over here



LastUpdatedDate
2011-03-08 10:47:54.010

2011-03-08 00:00:00.000
2011-03-08 12:19:41.417
it is not showing the rows inserted after 12


 stored proc return                       tbl_note   return

   03-08-2011                          2011-03-08 10:47:54.010
Ope this solves the problem.
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 < @LastUpdatedTo) 
      --BEGIN 
      --IF (@LastUpdatedFrom IS NOT NULL ) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
    ELSE IF (@LastUpdatedFrom = @LastUpdatedTo) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
  END 

--end 
PRINT @SQL 

EXEC( @SQL) 

--END 
GO

Open in new window

its strange still its not showing the other two records when i execute the following.Only one record is displayed.it seems the problem of time
exec DBSP_GetNote '','',null,null,'','03/08/2011','03/08/2011'
Can you alter the proc like this and post the SQL.
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 < @LastUpdatedTo) 
      --BEGIN 
      --IF (@LastUpdatedFrom IS NOT NULL ) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
    ELSE IF (@LastUpdatedFrom = @LastUpdatedTo) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END 
  END 

--end 
PRINT @SQL 

--EXEC( @SQL) 

--END 
GO

Open in new window

execute the proc with same parameters.
exec DBSP_GetNote '','',null,null,'','03/08/2011','03/08/2011'

Open in new window

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  AND CONVERT(varchar,n.LastUpdatedDate,112) = '20110308'
Nowrun this statement and check the result. Are you getting all recrods for 03/08/2011?
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 
       AND CONVERT(VARCHAR,n.LastUpdatedDate,112) = '20110308'

Open in new window

no just one out of three
LastUpdatedDate
03-08-2011
Ok. The problem is not with the SP. In fact its with your SQL. Change the other three INNER JOINs to LEFT JOIN if you want all three records.
Run this query and see the result.
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 
       LEFT JOIN TBL_eBid_Status s 
         ON n.StatusID = s.StatusID 
       LEFT JOIN dbo.TBL_NoteDivision nd 
         ON n.NoteID = nd.NoteID 
       LEFT 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 
       AND CONVERT(VARCHAR,n.LastUpdatedDate,112) = '20110308'

Open in new window

yes i got the result

but when keep this sql in stored procedure and executed
exec DBSP_GetNote '','',null,null,'','03/08/2011','03/08/2011'            i got all results plus 03-09-2011




LastUpdatedDate
03-08-2011

03-08-2011

03-09-201
03-08-2011

03-09-2011

03-09-2011
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial