Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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
0
sqlcurious
Asked:
sqlcurious
  • 12
  • 11
  • +1
1 Solution
 
wdosanjosCommented:
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

0
 
kamindaCommented:
Try passing your date in this format 'yyyy-dd-mm'
0
 
sqlcuriousAuthor Commented:
it did not return any rows
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
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
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
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
0
 
SharathData EngineerCommented:
Can you post the SQL with your PRINT statement?
0
 
sqlcuriousAuthor Commented:
i didnot get u .........there is a print statement in the sql.If u are asking anything else please let me know
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
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'
0
 
SharathData EngineerCommented:
Thats strange. Did you really pass 03/04/2011. If so, how did you get 20110308 in your query?
0
 
sqlcuriousAuthor Commented:
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
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:

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
0
 
sqlcuriousAuthor Commented:
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
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
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'
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
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'
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
no just one out of three
LastUpdatedDate
03-08-2011
0
 
SharathData EngineerCommented:
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

0
 
sqlcuriousAuthor Commented:
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
0
 
SharathData EngineerCommented:
Did you alter the SP like 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                        
                  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 ' 
     
    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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 12
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now