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

asked on

stored proc help

i have a problem in stored proc the date range is not working if i execute following  in the procedure.I have attached the output too please check

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



output.xls.xlsx
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
what is the datatype of the date...

you haven't got the date range coding active in the procedure you've given us...

.

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image


You need to add time to your @LastUpdatedTo variable to reflect the end of the day

'03/04/2011 23:59:59'
IF (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
 BEGIN
   SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+'''' 
 END 
 ELSE
 BEGIN
   set @LastUpdatedFrom = DATEADD(SECOND, -1, DATEADD(DD, 1, @LastUpdatedFrom))
   SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''  
                                    
 END

Open in new window

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image
sorry, updatedto

IF (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
 BEGIN
   SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''
 END
 ELSE
 BEGIN
   set @LastUpdatedTo= DATEADD(SECOND, -1, DATEADD(DD, 1, @LastUpdatedTo))
   SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''  
                                   
 END
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

sorry didnot help


--exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'
ALTER PROCEDURE [dbo].[DBSP_GetNote]      
(                
 @NoteDescription varchar(200)='',                
 @NoteDefault bit,                
 @NoteStatus int,                
 @LastUpdatedBy int,
 @DivisionIDSearch int,      
 @LastUpdatedFrom Datetime = NULL,        
 @LastUpdatedTo Datetime = NULL
   
)                
as                
begin                
declare @SQL varchar(8000)                    
               
Set @SQL = 'SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.NoteID) as DivisionName, s.StatusDescription, n.NoteDefault,      
   u1.UserFirstName + ' + '''  ''' + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.CreatedDate,110) as CreatedDate,    
 u2.UserFirstName + ' + '''  ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.LastUpdatedDate,110) as LastUpdatedDate                      
  FROM TBL_Note AS n                    
  INNER JOIN TBL_eBid_Status s on n.StatusID = s.StatusID    
  INNER JOIN dbo.TBL_NoteDivision nd on n.NoteID = nd.NoteID      
  INNER JOIN TBL_Division d on nd.DivisionID = d.DivisionID              
  LEFT JOIN TBL_eBid_User u1 on n.CreatedBy = u1.UserID            
  LEFT JOIN TBL_eBid_User u2 on n.LastUpdatedBy = u2.UserID                    
  Where n.NoteID > 0 '                
               
if(@NoteDescription is not null and @NoteDescription <> '')                
 Begin                
   Set @SQL =  @SQL + ' and n.NoteDescription LIKE ''%'+@NoteDescription+'%'''                
 End                
               
if(@NoteDefault = 1)        
 Begin                
   Set @SQL =  @SQL + ' and n.NoteDefault =  '+ convert(varchar(5), @NoteDefault)                
 End                
               
if(@NoteStatus > 0)                
 Begin                
   Set @SQL =  @SQL + ' and n.StatusID =  '+ convert(varchar(5),@NoteStatus )                
 End                
               
if(@LastUpdatedBy > 0)                
 Begin                
   Set @SQL =  @SQL + ' and n.LastUpdatedBy =  ' + convert(varchar(5),@LastUpdatedBY )                
 End    
IF(@DivisionIDSearch <> 0)        
BEGIN          
   SET @SQL = @SQL + ' and d.DivisionID = '+convert(varchar(5), @DivisionIDSearch)          
END      
         
 --If @LastUpdatedFrom Is Not Null        
 --Begin          
 -- Set @SQL = @SQL + ' and n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''          
 --End        
         
 --If @LastUpdatedTo Is Not Null          
 --Begin          
 -- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''          
 --End  
 
 --End                    
 
 

 --IF (@LastUpdatedFrom IS NOT NULL AND  @LastUpdatedTo  IS NOT NULL)
      --                  BEGIN
      --                              IF (@LastUpdatedFrom = @LastUpdatedTo )
      --                                    BEGIN
            
      --                                          SET @SQL=@SQL + ' AND  n.LastUpdatedDate ='''+convert(varchar(30),@LastUpdatedFrom,102)+''''
      --                                    END
      --                              ELSE
      --                                    BEGIN
      --                                          SET  @SQL =  @SQL + 'AND   n.LastUpdatedDate  BETWEEN ' + '''' +  +convert(varchar(30),@LastUpdatedFrom,102)+''''  + '''' + ' AND ' + '''' +   +convert(varchar(30),@LastUpdatedFrom,102)+''''
      --                                END            
      --                  END                     
   
   IF (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
 BEGIN
   SET @SQL = @SQL+ ' AND n.LastUpdatedDate >='''+convert(varchar(30),@LastUpdatedFrom,101)+''''
 END
 ELSE
 BEGIN
   set @LastUpdatedTo= DATEADD(SECOND, -1, DATEADD(DD, 1, @LastUpdatedTo))
   SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''  
 end                                  
 END
   
   --end
print @SQL                
exec (@SQL)  
   
   
    --END

   
   
   
    when i execute this :exec DBSP_GetNote '','',null,null,'','03/04/2011','03/04/2011'
it  gave the following result


03-03-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
03-04-2011
03-04-2011

GO
Avatar of Sharath S
Sharath S
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,101)+''''          
    --End          
    --If @LastUpdatedTo Is Not Null           
    --Begin           
    -- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@LastUpdatedTo,101)+''''          
    --End   
    --End                     
    --IF (@LastUpdatedFrom IS NOT NULL AND  @LastUpdatedTo  IS NOT NULL) 
    --                  BEGIN 
    --                              IF (@LastUpdatedFrom = @LastUpdatedTo ) 
    --                                    BEGIN 
    --                                          SET @SQL=@SQL + ' AND  n.LastUpdatedDate ='''+convert(varchar(30),@LastUpdatedFrom,102)+'''' 
    --                                    END 
    --                              ELSE 
    --                                    BEGIN 
    --                                          SET  @SQL =  @SQL + 'AND   n.LastUpdatedDate  BETWEEN ' + '''' +  +convert(varchar(30),@LastUpdatedFrom,102)+''''  + '''' + ' AND ' + '''' +   +convert(varchar(30),@LastUpdatedFrom,102)+'''' 
    --                                END             
    --                  END 
    IF (@LastUpdatedFrom IS NOT NULL 
        AND @LastUpdatedTo IS NOT NULL) 
      --BEGIN 
      --IF (@LastUpdatedFrom IS NOT NULL ) 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(DATE,n.LastUpdatedDate) >=''' + CONVERT(VARCHAR,CONVERT(DATE,@LastUpdatedFrom)) + ''''
      END 
    ELSE 
      BEGIN 
        SET @SQL = @SQL + ' AND CONVERT(DATE,n.LastUpdatedDate) <= ''' + CONVERT(VARCHAR,CONVERT(DATE,@LastUpdatedTo)) + ''''
      END 
  END 

--end 
PRINT @SQL 

EXEC( @SQL) 

--END 
GO

Open in new window

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
try this

but consider also that you need to probably include a time component on the from and to datetimes...

otherwise with the "same" date you are only looking at the data created at midnight on thaty day...
create PROCEDURE [dbo].[DBSP_GetNote]      
(                
 @NoteDescription varchar(200)='',                
 @NoteDefault bit,                
 @NoteStatus int,                
 @LastUpdatedBy int, 
 @DivisionIDSearch int,       
 @LastUpdatedFrom Datetime = NULL,        
 @LastUpdatedTo Datetime = NULL
   
)                 
as                
begin   
             
declare @SQL varchar(8000)   
       ,@fromdate varchar(30),@todate varchar(30) 
                       
                
Select @fromdate = CONVERT(char(9),@lastupdatedfrom,112)+CONVERT(char(8),@lastupdatedfrom,108)
      ,@todate = CONVERT(char(9),@lastupdatedto,112)+CONVERT(char(8),@lastupdatedto,108)
      
Select @SQL = 'SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.NoteID) as DivisionName,' 
             +'s.StatusDescription, n.NoteDefault, u1.UserFirstName + '
             + '''  ''' 
             + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.CreatedDate,110) as CreatedDate,'
             + ' u2.UserFirstName + ' 
             + '''  ''' 
             + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.LastUpdatedDate,110) as LastUpdatedDate'                     
             + ' FROM TBL_Note AS n '                   
             +' INNER JOIN TBL_eBid_Status s on n.StatusID = s.StatusID'     
             +' INNER JOIN dbo.TBL_NoteDivision nd on n.NoteID = nd.NoteID'       
             +' INNER JOIN TBL_Division d on nd.DivisionID = d.DivisionID'              
             +' LEFT JOIN TBL_eBid_User u1 on n.CreatedBy = u1.UserID'            
             +' LEFT JOIN TBL_eBid_User u2 on n.LastUpdatedBy = u2.UserID'                    
             +' Where n.NoteID > 0 '                
                
+case when (@NoteDescription is not null and @NoteDescription <> '')                
      then  ' and n.NoteDescription LIKE ''%'+@NoteDescription+'%'''                
      else ''
 End                
                
+case when (@NoteDefault = 1)        
      then ' and n.NoteDefault =  '+ convert(varchar(5), @NoteDefault)                
      else ''
 End                
                
+case when (@NoteStatus > 0)                
      then  ' and n.StatusID =  '+ convert(varchar(5),@NoteStatus )                
      else ''
 End                
                
+case when (@LastUpdatedBy > 0)                
      then ' and n.LastUpdatedBy =  ' + convert(varchar(5),@LastUpdatedBY )                
      else ''
 End     
+case when (@DivisionIDSearch <> 0)        
      then ' and d.DivisionID = '+convert(varchar(5), @DivisionIDSearch)          
      else ''
END       
         
+case when (@LastUpdatedFrom IS NULL and  @LastUpdatedTo IS NULL)
      then ''
      else ' and n.lastupdateddate ' 
           + case 
                   when (@LastUpdatedFrom IS NOT NULL and  @LastUpdatedTo IS NOT NULL)
                   then  'between '''+@fromdate+''' and '''+@todate 
                   When @lastupdatedto IS not null 
                   then  '<= '''+@todate
                   else  
                         '>= '''+@fromdate                             
                   end
           + ''''        
      end                                           
            
print @SQL                
exec(@SQL)   
 
 End   
    
 Return    

GO

Open in new window

Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER



i got error

Msg 243, Level 16, State 1, Procedure DBSP_GETNOTE, Line 182
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Procedure DBSP_GETNOTE, Line 186
Type DATE is not a defined system type.
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
confirm the edition and version of sql server you are using
and also the compatability level of the database ....


date is a valid datatype for sql 2008 database not running at a downgraded compatabilty level.

also confirm what the datatypes are for your lastupdateddate column
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER



Msg 241, Level 16, State 1, Procedure DBSP_GetNote, Line 22
Conversion failed when converting datetime from character string.


The datatype for lastupdated date column is datetime

i did select @@version and it says
Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)   Mar  3 2007 18:40:02   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I am connecting to my remote server and the db is in  2005 sql server
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

now it didnot show any error but i did not return any rows
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
you have the question in the wrong zone then it should be a sql server 2005 zone.


can you please post the display sql , and your invcation parameters...
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

even though it is in sql 2005 i think it doesnot matter much in case of  datatype.Can u please check in the SP why it is not returning any rows?
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