sqlcurious
asked on
help in procedure
stored proc help
i have a problem in stored proc the date range is not working if i execute following in the procedure.NO rows are selected
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.Crea tedDate,11 0) as CreatedDate,
u2.UserFirstName + ' + ''' ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.Last UpdatedDat e,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),@NoteSt atus )
End
if(@LastUpdatedBy > 0)
Begin
Set @SQL = @SQL + ' and n.LastUpdatedBy = ' + convert(varchar(5),@LastUp datedBY )
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), @LastUpdat edFrom,101 )+''''
--End
--If @LastUpdatedTo Is Not Null
--Begin
-- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@L astUpdated To,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),@ LastUpdate dFrom,102) +''''
-- END
-- ELSE
-- BEGIN
-- SET @SQL = @SQL + 'AND n.LastUpdatedDate BETWEEN ' + '''' + +convert(varchar(30),@Last UpdatedFro m,102)+''' ' + '''' + ' AND ' + '''' + +convert(varchar(30),@Last UpdatedFro m,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), @LastUpdat edFrom,101 )+''''
END
ELSE
BEGIN
SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@L astUpdated To,101)+'' ''
END
END
--end
print @SQL
exec (@SQL)
--END
GO
i have a problem in stored proc the date range is not working if i execute following in the procedure.NO rows are selected
exec DBSP_GetNote '','',null,null,'','03/04/
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/
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.
u1.UserFirstName + ' + ''' ''' + ' + u1.UserLastName as CreatedByUser, convert(varchar(10),n.Crea
u2.UserFirstName + ' + ''' ''' + ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.Last
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),@NoteSt
End
if(@LastUpdatedBy > 0)
Begin
Set @SQL = @SQL + ' and n.LastUpdatedBy = ' + convert(varchar(5),@LastUp
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),
--End
--If @LastUpdatedTo Is Not Null
--Begin
-- Set @SQL = @SQL + ' and n.LastUpdatedDate <= '''+convert(varchar(30),@L
--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),@
-- END
-- ELSE
-- BEGIN
-- SET @SQL = @SQL + 'AND n.LastUpdatedDate BETWEEN ' + '''' + +convert(varchar(30),@Last
-- 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),
END
ELSE
BEGIN
SET @SQL = @SQL + ' AND n.LastUpdatedDate <= '''+convert(varchar(30),@L
END
END
--end
print @SQL
exec (@SQL)
--END
GO
Try passing your date in this format 'yyyy-dd-mm'
ASKER
it did not return any rows
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(30),@LastUpdatedFrom,101) + ''''
END
ELSE
BEGIN
SET @SQL = @SQL + ' AND CONVERT(DATE,n.LastUpdatedDate) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,101) + ''''
END
END
--end
PRINT @SQL
EXEC( @SQL)
--END
GO
ASKER
First the error was date is not the system datatype then i changed to datetime
and executed the statement
exec DBSP_GetNote '','',null,null,'','03/04/ 2011','03/ 04/2011' and got following result
for lastupdateddate column which is not desired
03-04-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
and executed the statement
exec DBSP_GetNote '','',null,null,'','03/04/
for lastupdateddate column which is not desired
03-04-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
Check 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(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
END
ELSE
BEGIN
SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END
END
--end
PRINT @SQL
EXEC( @SQL)
--END
GO
ASKER
Its not working
when i execute this
exec DBSP_GetNote '','',null,null,'','03/04/ 2011','03/ 04/2011' it showed following result.i just took the column name.
LastUpdatedDate
03-04-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
when i execute this
exec DBSP_GetNote '','',null,null,'','03/04/
LastUpdatedDate
03-04-2011
03-04-2011
03-04-2011
03-07-2011
03-08-2011
03-09-2011
03-09-2011
03-09-2011
Can you post the SQL with your PRINT statement?
ASKER
i didnot get u .........there is a print statement in the sql.If u are asking anything else please let me know
Alter your SP like this and run the SP. Then post the statement.
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
--IF (@LastUpdatedFrom IS NOT NULL )
BEGIN
SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) >=''' + CONVERT(VARCHAR(30),@LastUpdatedFrom,112) + ''''
END
ELSE
BEGIN
SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END
END
--end
PRINT @SQL
--EXEC( @SQL)
--END
GO
ASKER
i executed following
exec DBSP_GetNote '','',null,null,'','03/04/ 2011','03/ 04/2011'
and got the result
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.Crea tedDate,11 0) as CreatedDate, u2.UserFirstName + ' ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.Last UpdatedDat e,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 AND CONVERT(varchar,n.LastUpda tedDate,11 2) >='20110308'
exec DBSP_GetNote '','',null,null,'','03/04/
and got the result
SELECT DISTINCT n.NoteID, n.NoteDescription, dbo.fneBid_NoteDivision(n.
Thats strange. Did you really pass 03/04/2011. If so, how did you get 20110308 in your query?
ASKER
thats tru if i exceutw following
exec DBSP_GetNote '','',null,null,'','03/08/ 2011','03/ 08/2011'.I get following
LastUpdatedDate
03-08-2011
03-09-2011
03-09-2011
03-09-2011
exec DBSP_GetNote '','',null,null,'','03/08/
LastUpdatedDate
03-08-2011
03-09-2011
03-09-2011
03-09-2011
Run this.
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
--IF (@LastUpdatedFrom IS NOT NULL )
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
BEGIN
SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) <= ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END */
END
--end
PRINT @SQL
EXEC( @SQL)
--END
GO
ASKER
It gave the result partially.When i execute exec DBSP_GetNote '','',null,null,'','03/08/
LastUpdatedDate
03-08-2011
I am taking the data from the table 'tbl_note' and when i exceute select * from tbl_note i found following data for the date 3/08/11 along with the time.I think time is playing the role over here
LastUpdatedDate
2011-03-08 10:47:54.010
2011-03-08 00:00:00.000
2011-03-08 12:19:41.417
ASKER
it is not showing the rows inserted after 12
stored proc return tbl_note return
03-08-2011 2011-03-08 10:47:54.010
stored proc return tbl_note return
03-08-2011 2011-03-08 10:47:54.010
Ope this solves the problem.
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 < @LastUpdatedTo)
--BEGIN
--IF (@LastUpdatedFrom IS NOT NULL )
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 = @LastUpdatedTo)
BEGIN
SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END
END
--end
PRINT @SQL
EXEC( @SQL)
--END
GO
ASKER
its strange still its not showing the other two records when i execute the following.Only one record is displayed.it seems the problem of time
exec DBSP_GetNote '','',null,null,'','03/08/ 2011','03/ 08/2011'
exec DBSP_GetNote '','',null,null,'','03/08/
Can you alter the proc like this and post the SQL.
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 < @LastUpdatedTo)
--BEGIN
--IF (@LastUpdatedFrom IS NOT NULL )
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 = @LastUpdatedTo)
BEGIN
SET @SQL = @SQL + ' AND CONVERT(varchar,n.LastUpdatedDate,112) = ''' + CONVERT(VARCHAR(30),@LastUpdatedTo,112) + ''''
END
END
--end
PRINT @SQL
--EXEC( @SQL)
--END
GO
execute the proc with same parameters.exec DBSP_GetNote '','',null,null,'','03/08/2011','03/08/2011'
ASKER
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.Crea tedDate,11 0) as CreatedDate, u2.UserFirstName + ' ' + u2.UserLastName as LastUpdatedByUser, convert(varchar(10),n.Last UpdatedDat e,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 AND CONVERT(varchar,n.LastUpda tedDate,11 2) = '20110308'
Nowrun this statement and check the result. Are you getting all recrods for 03/08/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
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
AND CONVERT(VARCHAR,n.LastUpdatedDate,112) = '20110308'
ASKER
no just one out of three
LastUpdatedDate
03-08-2011
LastUpdatedDate
03-08-2011
Ok. The problem is not with the SP. In fact its with your SQL. Change the other three INNER JOINs to LEFT JOIN if you want all three records.
Run this query and see the result.
Run this query and see the result.
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) = '20110308'
ASKER
yes i got the result
but when keep this sql in stored procedure and executed
exec DBSP_GetNote '','',null,null,'','03/08/ 2011','03/ 08/2011' i got all results plus 03-09-2011
LastUpdatedDate
03-08-2011
03-08-2011
03-09-201
03-08-2011
03-09-2011
03-09-2011
but when keep this sql in stored procedure and executed
exec DBSP_GetNote '','',null,null,'','03/08/
LastUpdatedDate
03-08-2011
03-08-2011
03-09-201
03-08-2011
03-09-2011
03-09-2011
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window