error in Stored proc

hi experts,

My dateRange for the lastupdatedDate is not showing correctly when i execute the attached stored proc .I have attached the screen shot of he output to.I want to display the data
for lastupdated date which have null value.The data is displaying but date range is not correct.How can i modify the proc please help
notesstored.txt
Book1.xls
sqlcuriousAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lenordisteCommented:
what do you mean by "lastUpdatedDate is not showing correctly"? what format were you expecting? Can you give us an example?
Ephraim WangoyaCommented:

Really depends on what you want to do if the LastUpdatedFrom and LastUpdatedTo parameters are null

Try with this
USE [eBidManager]
GO
/****** Object:  StoredProcedure [dbo].[DBSP_GetNote]    Script Date: 04/01/2011 17:00:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



--select * from tbl_note
--exec DBSP_GetNote '','',null,null,'','03/31/2011','04/01/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                        
                  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 null and @LastUpdatedTo is not null )
      BEGIN
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <=''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END
    ELSE IF (@lastUpdatedFrom is NOT null and @LastUpdatedTo is null )
      BEGIN
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
      END      
    IF (@lastUpdatedFrom is not null and @LastUpdatedFrom < @LastUpdatedTo )
      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 is not null and @LastUpdatedFrom = @LastUpdatedTo )
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END
  END

PRINT @SQL 

EXEC( @SQL)

Open in new window

sqlcuriousAuthor Commented:
it didnot give me the results.It gave me only 4 results.As you can see in the attached file in excel,the lastupdateddate has null values.The procedure does not retum the results for those lastupdatedate  that has null values


if i execute following statement

exec DBSP_GetNote '','',null,null,'','03/31/2011','04/01/2011' it should populate all the results that lies between anon those dates .In addition it should also show the result for the column 'LastUpdatedDate that has null values.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

gladfellowCommented:
Replace the block from line 60 till line 76 in the SP text with the following block

**** SUGGESTED ALTERED CODE STARTS

SET @SQL = @SQL + ' AND (n.LastUpdatedDate is null or '
IF (@lastUpdatedFrom is null and @LastUpdatedTo is not null )
BEGIN
	SET @SQL = @SQL + ' CONVERT(varchar,n.LastUpdatedDate,112) <=''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END
ELSE IF (@lastUpdatedFrom is NOT null and @LastUpdatedTo is null )
	BEGIN
	SET @SQL = @SQL + ' CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
END      
IF (@lastUpdatedFrom is not null and @LastUpdatedFrom < @LastUpdatedTo )
BEGIN
	SET @SQL = @SQL + ' (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 is not null and @LastUpdatedFrom = @LastUpdatedTo )
BEGIN 
	SET @SQL = @SQL + ' CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END
SET @SQL = @SQL + ')'

**** SUGGESTED ALTERED CODE ENDS

Open in new window

sqlcuriousAuthor Commented:
it says error


Msg 102, Level 15, State 1, Procedure DBSP_GetNote, Line 154
Incorrect syntax near ')'.
gladfellowCommented:
Request you to figure out the brackets that do not match. I do not have access now to a tool that would help me syntax-check where the brackets give trouble...

Having said that ... the approach was what I suggested. If you want all the rows with LastUpdatedDate as null also to be included in the results, you need to include the following condition also before your comparison of LastUpdatedDate with @LastUpdatedFrom and @LastUpdatedTo

Condition to be included:
(n.LastUpdatedDate is null or {YOUR EXISTING CONDITION})

This would mean before comparing LastUpdatedDate with @LastUpdatedFrom and @LastUpdatedTo, the where clause would evaluate if n.LastUpdatedDate is null and if it is, it would return that row in the result straightaway.

Hope it helps.
Ephraim WangoyaCommented:
try this
USE [eBidManager]
GO
/****** Object:  StoredProcedure [dbo].[DBSP_GetNote]    Script Date: 04/01/2011 17:00:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



--select * from tbl_note
--exec DBSP_GetNote '','',null,null,'','03/31/2011','04/01/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) 
    DECLARE @UpdateFilter varchar(512)
     
    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 null and @LastUpdatedTo is not null )
      BEGIN
        SET @UpdateFilter = ' AND ((CONVERT(varchar,n.LastUpdatedDate,112) <=''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''')' 
      END
    ELSE IF (@lastUpdatedFrom is NOT null and @LastUpdatedTo is null )
      BEGIN
        SET @UpdateFilter = ' AND ((CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''')'
      END      
    IF (@lastUpdatedFrom is not null and @LastUpdatedFrom < @LastUpdatedTo )
      BEGIN
        SET @UpdateFilter = ' AND (((CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''')'
        SET @UpdateFilter = @UpdateFilter + ' AND (CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + '''))'
      END     
    ELSE IF (@lastUpdatedFrom is not null and @LastUpdatedFrom = @LastUpdatedTo )
      BEGIN 
        SET @UpdateFilter = ' AND ((CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''')'
      END
      
    if @UpdateFilter IS NOT NULL
      SET @SQL = @SQL + @UpdateFilter + ' OR (n.LastUpdatedDate IS NULL))'
  END

PRINT @SQL 

EXEC( @SQL)

Open in new window

lenordisteCommented:
you can also use ISNULL to set the null values equal to the dates you are comparing with. It's a bit of a "trick" but here it is (begins at line 62) :
      BEGIN
        SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(n.LastUpdatedDate,@LastUpdatedFrom),112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
        SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(n.LastUpdatedDate,@LastUpdatedTo),112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END

Open in new window

sqlcuriousAuthor Commented:
hi lenordiste

it is showing error .It says must declare a scalar variable @lastUpdatedFrom

I think it is problem since it is inside the dynamic sql.

 SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(n.LastUpdatedDate,@LastUpdatedFrom),112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
        SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(n.LastUpdatedDate,@LastUpdatedTo),112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
lenordisteCommented:
you're right, I don't have SQL Server close to hand so it's hard to do this without any test but try this:
BEGIN
        SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(n.LastUpdatedDate,'''+CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + '''),112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
        SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(n.LastUpdatedDate,'''+CONVERT(VARCHAR(30),@LastUpdatedTo,112) + '''),112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
      END

Open in new window


you might want to tweak this a bit to avoid converting the date to varchar too many times.
gladfellowCommented:
By the way, why would you need a dynamic SQL in the first place? It seems easier to express this requirement as a simple straightforward SQL itself.

Please check out the suggested procedure text in the attached file.

Best wishes.
 Error-in-stored-proc---corrected.txt
lenordisteCommented:
agreed, as suggested by gladfellow it's better to avoid dynamic SQL (execution plan optimisation...) and his solution seems straightforward.
gladfellowCommented:
Thanks for your comment!

- Venkat
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
it seems you there are two problems
Firs you check in if is not correct against date parameter IF (@lastUpdatedFrom is null and @LastUpdatedFrom < @LastUpdatedTo )
I think it should be IF (@lastUpdatedFrom is not null and @LastUpdatedFrom < @LastUpdatedTo )

Problem is that you are converting date to text and those are compared as text not as dates so, you have to convert them back to dates before compare
Thanks

IF (@lastUpdatedFrom is not null and @LastUpdatedFrom < @LastUpdatedTo )
     
      BEGIN
        SET @SQL = @SQL + ' AND CONVERT(datetime, CONVERT(varchar,n.LastUpdatedDate,112)) >=CONVERT(datetime, ''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''')'
        SET @SQL = @SQL + ' AND CONVERT(datetime, CONVERT(varchar,n.LastUpdatedDate,112)) <= CONVERT(datetime, ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''')'
      END
     
     
    ELSE IF (@LastUpdatedFrom = @LastUpdatedTo )
      BEGIN
        SET @SQL = @SQL + ' AND CONVERT(datetime, CONVERT(varchar,n.LastUpdatedDate,112)) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''')'
      END
Your complete code may lookl like 


USE [eBidManager]
GO
/****** Object:  StoredProcedure [dbo].[DBSP_GetNote]    Script Date: 04/01/2011 17:00:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



--select * from tbl_note
--exec DBSP_GetNote '','',null,null,'','03/31/2011','04/01/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                        
                  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 and @LastUpdatedFrom < @LastUpdatedTo )
     
      BEGIN
        SET @SQL = @SQL + ' AND CONVERT(datetime, CONVERT(varchar,n.LastUpdatedDate,112)) >=CONVERT(datetime, ''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''')'
        SET @SQL = @SQL + ' AND CONVERT(datetime, CONVERT(varchar,n.LastUpdatedDate,112)) <= CONVERT(datetime, ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''')'
      END
      
      
    ELSE IF (@LastUpdatedFrom = @LastUpdatedTo )
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(datetime, CONVERT(varchar,n.LastUpdatedDate,112)) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''')'
      END
    --ELSE IF (@LastUpdatedFrom = NULL)
    --  BEGIN 
    --    SET @SQL = @SQL + ' AND CONVERT(varchar,n.CreateDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
    --  END
  END

PRINT @SQL 

EXEC( @SQL)

Open in new window

sqlcuriousAuthor Commented:
hi gladfellow

your suggested query didnot give me any result,though there are records
sqlcuriousAuthor Commented:
i have small problem in the following procedure
If i execute following
exec DBSP_GetTermAndCondition '','','','','','04/04/2011','04/04/2011'  i dont get the records created on 04/04/04 ..what could be the problem
 but while populating table   select * from dbo.TBL_TermAndCondition   the result exists




--exec DBSP_GetTermAndCondition '','','','','','04/04/2011','04/04/2011'
       
ALTER PROCEDURE [dbo].[DBSP_GetTermAndCondition]          
(            
@TermDescription VARCHAR(200) = '',          
@TermDefault BIT,            
@TermStatus INT,          
@LastUpdatedBy INT,        
@DivisionID varchar(200)='',    
@LastUpdatedFrom DateTime = null,    
@LastUpdatedTo DateTime=null    
 )            
AS          
BEGIN            
DECLARE @SQL VARCHAR(8000)
DECLARE @TEMP VARCHAR(200)            
           
           
SET @SQL = '        
SELECT DISTINCT T.TermID, T.TermDescription, T.StatusID, S.StatusDescription, T.TermDefault,          
u.UserFirstName + '+ ''' '''+ ' + u.UserLastName as CreatedBy, T.CreatedDate,    
ur.UserFirstName + '+ ''' '''+ '+ u.UserLastName as LastUpdatedBy,T.LastUpdatedDate, dbo.fneBid_TermDivision(T.TermID) DivisionName      
FROM TBL_TermAndCondition AS T              
left join TBL_eBid_Status S on T.StatusID = S.StatusID        
left join TBL_TermAndConditionDivision tcd on T.TermID = tcd.TermID      
left join TBL_Division d on tcd.DivisionID = d.DivisionID      
left join TBL_eBid_User u  on T.createdby=u.userid        
left join TBL_eBid_User ur on T.LastUpdatedBy=ur.userid                
Where T.TermID > 0 '            
     
IF(@TermDescription <> '')
 BEGIN          
   SET @SQL = @SQL + ' and TermDescription like ' + '''' + '%' + @TermDescription + '%'  + ''''          
 END            
           
IF(@TermDefault = 1 )
 BEGIN            
   SET @SQL =  @SQL + ' and TermDefault =  '+ convert(varchar(5), @TermDefault)
 END
           
IF(@TermStatus != 0)
 BEGIN
   SET @SQL =  @SQL + ' and T.StatusID =  '+ convert(varchar(10),@TermStatus )            
 END          
           
IF(@LastUpdatedBy > 0)            
 BEGIN          
   SET @SQL =  @SQL + ' and T.LastUpdatedBy =  ' + convert(varchar(5),@LastUpdatedBy )            
 END        
       
IF(@DivisionID <> 0)        
BEGIN          
   SET @SQL = @SQL + ' and d.DivisionID like ' + '''' + '%' + @DivisionID + '%'  + ''''          
END    

IF (@LastUpdatedFrom < @LastUpdatedTo)
     
        BEGIN
          SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(t.LastUpdatedDate,'''+CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + '''),112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
          SET @SQL = @SQL + ' AND CONVERT(varchar,isnull(t.LastUpdatedDate,'''+CONVERT(VARCHAR(30),@LastUpdatedTo,112) + '''),112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
        END
          ELSE IF (@LastUpdatedFrom = @LastUpdatedTo)
        BEGIN
       
          SET @SQL = @SQL + ' AND CONVERT(varchar,t.createdDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
          SET @SQL = @SQL + ' AND CONVERT(varchar,t.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
          END
        
             ELSE IF (@LastUpdatedFrom = NULL)
          BEGIN
              SET @SQL = @SQL + ' AND CONVERT(varchar,t.CreatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
          SET @SQL = @SQL + ' AND CONVERT(varchar,t.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
          END
   END

PRINT @SQL
EXEC (@SQL)


lenordisteCommented:
this condition:
IF (@LastUpdatedFrom < @LastUpdatedTo)
should be:
IF (@LastUpdatedFrom <= @LastUpdatedTo)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlcuriousAuthor Commented:
thnks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.