stored proce help needed

hi experts

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)

sqlcuriousAsked:
Who is Participating?
 
cyberkiwiCommented:
My comments point to improvements which will make it run faster.

What you still haven't done is find out why it does not show results (the other part of my comment). There is no point making it return no results, faster.  Make it return results first - check according to the pointers I gave.
0
 
cyberkiwiCommented:
Show the output of PRINT @SQL
0
 
cyberkiwiCommented:
I'm sure you have a typo

04/04/2011','04/04/2011'  i dont get the records created on 04/04/04

2004 and 2011 are 7 years apart otherwise.

Your PRINT will produce

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  AND CONVERT(varchar,t.createdDate,112) = '20110404' AND CONVERT(varchar,t.LastUpdatedDate,112) = '20110404'

Open in new window


which is really limited only by 3 conditions

T.TermID>0
CONVERT(varchar,t.createdDate,112) = '20110404'
CONVERT(varchar,t.LastUpdatedDate,112) = '20110404'

If EITHER OF TermID or t.createdDate is NULL, you don't get a result. That's how NULL's work, because they are incomparable.  If TermID is <=0, no result either, ditto t.CreatedDate.

Check those first.

Another point I would make is that your query is not index friendly.  Doing CONVERT(Varchar, [date]) that way will negate the possibility of using indexes. You should generate the SQL statement like this:

AND t.createdDate >= '20110404'
AND t.createdDate <  dateadd(d,'20110404' ,1)
AND t.LastUpdatedDate >= '20110404'
and  t.LastUpdatedDate < dateadd(d, '20110404',1)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sqlcuriousAuthor Commented:
exec DBSP_GetNote '','',null,null,'','04/04/2011','04/04/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                        
                  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) = '20110404'
0
 
sqlcuriousAuthor Commented:


actually following is the result

exec DBSP_GetTermAndCondition '','','','','','04/04/2011','04/04/2011'


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  AND CONVERT(varchar,t.createdDate,112) = '20110404' AND CONVERT(varchar,t.LastUpdatedDate,112) = '20110404'
0
 
cyberkiwiCommented:
@sqlcurious

I got the output after some tinkering. Please see my comment above.
0
 
sqlcuriousAuthor Commented:
can you please  modify my stored proc  according to your comment
0
 
sqlcuriousAuthor Commented:
thnks
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.

All Courses

From novice to tech pro — start learning today.