March 18, 2008 at 9:29 am
Hi all,
I've got a Sproc I'd like to alter so that it will accept input parameters, at the moment it ignores the dates I pass in (exec uspfraudreport '04-mar-2008', '05-mar-2008') and returns rows from all dates. Here's the code I've got so far, I've left out the irrelevant stuff at the end;
USE [AlvysReport]
GO
/****** Object: StoredProcedure [dbo].[uspTestFraudReport] Script Date: 03/18/2008 09:43:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description: dbo.
-- =============================================
ALTER PROCEDURE [dbo].[uspFraudReport]
-- Add the parameters for the stored procedure here
(@StartTime datetime = null,
@EndTime datetime = null)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @DayBegining VarChar(50), @TodayDate VarChar(50)
Select @DayBegining = 'Monday'
Set @EndTime = Convert(varchar, getdate(),101)
Set @TodayDate = DateName(dw,@EndTime)
If @DayBegining = @TodayDate
Set @StartTime = DateAdd(dd, -3, @EndTime)
Else
Set @StartTime = DateAdd(dd, -1, @EndTime)
March 18, 2008 at 9:51 am
I am confused by this part
Set @TodayDate = DateName(dw,@EndTime)
Wouldn't you want @TodayDate to equal the day of the week for today's date?
Set @TodayDate = DateName(dw,GETDATE())
March 18, 2008 at 9:51 am
your sproc is accepting parameters. Without seeing all the code, it's hard to determine what might be wrong.
I'd suggest that you add a SELECT statement below this code to show the parameters and local variables values.
March 18, 2008 at 10:20 am
ALTER PROCEDURE [dbo].[uspFraudReport]
-- Add the parameters for the stored procedure here
(@StartTime datetime = null,
@EndTime datetime = null)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @DayBegining VarChar(50), @TodayDate VarChar(50)
Select @DayBegining = 'Monday'
Set @EndTime = Convert(varchar, getdate(),101)Set @TodayDate = DateName(dw,@EndTime)
If @DayBegining = @TodayDate
Set @StartTime = DateAdd(dd, -3, @EndTime)
Else
Set @StartTime = DateAdd(dd, -1, @EndTime)
Look at the bolded line above... Regardless of the date passed to the query, this line resets it to today's date. If you want this to be today's date if the parameter is null but @endTime otherwise then try this..
set @EndTime = isnull( @EndTime, Convert(varchar, getdate(),101) , @EndTime ) then do the same for @StartTime
DAB
March 18, 2008 at 10:31 am
No probs guys, sorted out now, I may have cried "help" here before exhausting in-house assets!
🙂
Jaybee.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply