Solved

stored proc help

Posted on 2011-03-09
13
323 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 500 total points
ID: 35113655
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 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