Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

stored proc help

Posted on 2011-03-09
13
1 solution
Medium Priority
?
325 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089495
what is the datatype of the date...

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

.

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35089502


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

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35089506
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
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

30 Day Free Trial
 

Author Comment

by:sqlcurious
ID: 35089648
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35089692
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

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089867
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

0
 

Author Comment

by:sqlcurious
ID: 35089880


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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089910
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
0
 

Author Comment

by:sqlcurious
ID: 35089989


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
0
 

Author Comment

by:sqlcurious
ID: 35090055
now it didnot show any error but i did not return any rows
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35090129
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...
0
 

Author Comment

by:sqlcurious
ID: 35090212
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?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35113655
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question
How to Reset a Lost SA Password in Microsoft SQL Server
Article by: Joseph
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
How to recover deleted rows in SQL Server
Article by: Yashwant
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
How to Create Calendar Reports from Access Queries
Video by: crystal
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to ask an Anonymous Question
Video by: Experts Exchange
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Advertise Here

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here