asked on

simple date range prompt in sql view

I want my view to prompt me for a date range when I run it. Currently the dates are hard coded.
SELECT TOP (100) PERCENT tDate, tActivityType, tUnits AS Total
FROM dbo.AxTransaction
WHERE (tUnits <> 0) AND (tActivityType = 1) AND (tDate >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)) AND (tDate <= CONVERT(DATETIME, '2010-12-31 00:00:00', 102))

How do I replace hard coded dates with prompts or parameters?

Thanks

Sharath S

You cannot do that in view. Create an sp and pass the dates as parameters.

kaminda

You will have to use either a function of sp to do this. My advice is to use an Table Valued Function so it can be used as a view in your queries.

Here is TBV Function script for your view,

FUNCTION [dbo].[fnGetAxTransaction]
(
	@FromDate DateTime,
    @ToDate DateTime
)


RETURNS  TABLE
AS 
RETURN
(
SELECT     TOP (100) PERCENT tDate, tActivityType, tUnits AS Total
FROM         dbo.AxTransaction
WHERE     (tUnits <> 0) AND (tActivityType = 1) AND (tDate >= @FromDate AND (tDate <= @ToDate)
)

Open in new window

Anthony Perkins

And please lose the TOP (100) PERCENT, it is pointless.

mbrennan_pacbell_net

ASKER

kaminda,

Thanks your solution worked somwhat. I was able to create the table value funcation based on your reply. However when I try to use it in a view, I get the following error:

"Incorrect Syntax Near ","

I am a SQL nube, so be gentle with me.

Anthony Perkins

The paranthesis are wrong it should be:
WHERE tUnits <> 0
AND tActivityType = 1
AND tDate BETWEEN @FromDate AND @ToDate

ASKER CERTIFIED SOLUTION

kaminda

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial