| Author |
 Topic  |
|
|
silentbob
Starting Member
18 Posts |
 Posted - 01/16/2013 : 04:40:06
|
Hi
I have a report that allows the user to set a date range and input a telephone number. These are passed as parameters to a stored procedure which then displays the results. I want to allow the user to omit the telephone number if they need to and just get data back for the date range selected.
THis is the stored procedure.
ALTER PROCEDURE [dbo].[CallLogging]
-- Add the parameters for the stored procedure here
@startdate datetime,
@enddate datetime,
@ExtNumber nvarchar(50)
AS
BEGIN
SELECT [DateTime]
,CONVERT(varchar, DATEADD(ms, [Duration] * 1000, 0),108) as Duration
,[DialledNumber]
,[Originator]
FROM [SwitchData].[dbo].[CallData]
WHERE ([DateTime] BETWEEN @startdate AND @enddate) AND (rtrim(ltrim(Originator)) like @ExtNumber) OR
([DateTime] BETWEEN @startdate AND @enddate) AND (rtrim(ltrim(DialledNumber)) like @ExtNumber)
END |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 01/16/2013 : 05:21:59
|
This?
WHERE ([DateTime] BETWEEN @startdate AND @enddate) AND
(
@ExtNumber IS NULL OR
rtrim(ltrim(Originator)) like @ExtNumber OR
rtrim(ltrim(DialledNumber)) like @ExtNumber
)
Too old to Rock'n'Roll too young to die. |
 |
|
|
silentbob
Starting Member
18 Posts |
Posted - 01/16/2013 : 05:30:18
|
Hi thanks for the suggestion but no this doesnt work get the following error in reporting services.
quote:
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset CallData. (rsErrorReadingNextDataRow)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
|
|
|
|
silentbob
Starting Member
18 Posts |
Posted - 01/17/2013 : 08:02:39
|
After much playing about I have fixed this myself. See code below.
ALTER PROCEDURE [dbo].[CallLogging]
@startdate datetime,
@enddate datetime,
@ExtNumber nvarchar(50)
AS
if @ExtNumber =''
BEGIN
SELECT [DateTime]
, case when duration = '' then '0'
when Duration like '%>%' then '0'
when duration <> '' then (CONVERT(varchar, DATEADD(ms, [Duration] * 1000, 0),108))end as Duration
,[DialledNumber]
,[Originator]
FROM [SwitchData].[dbo].[CallData]
WHERE ([DateTime]BETWEEN @startdate AND @enddate)
END
ELSE
if @ExtNumber <> ''
BEGIN
SELECT [DateTime]
,CONVERT(varchar, DATEADD(ms, [Duration] * 1000, 0),108) as Duration
,[DialledNumber]
,[Originator]
FROM [SwitchData].[dbo].[CallData]
WHERE ([DateTime] BETWEEN @startdate AND @enddate) AND (rtrim(ltrim(Originator)) like @ExtNumber) OR
([DateTime] BETWEEN @startdate AND @enddate) AND (rtrim(ltrim(DialledNumber)) like @ExtNumber)
END |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/17/2013 : 12:30:35
|
quote:
Originally posted by silentbob
After much playing about I have fixed this myself. See code below.
ALTER PROCEDURE [dbo].[CallLogging]
@startdate datetime,
@enddate datetime,
@ExtNumber nvarchar(50)
AS
if @ExtNumber =''
BEGIN
SELECT [DateTime]
, case when duration = '' then '0'
when Duration like '%>%' then '0'
when duration <> '' then (CONVERT(varchar, DATEADD(ms, [Duration] * 1000, 0),108))end as Duration
,[DialledNumber]
,[Originator]
FROM [SwitchData].[dbo].[CallData]
WHERE ([DateTime]BETWEEN @startdate AND @enddate)
END
ELSE
if @ExtNumber <> ''
BEGIN
SELECT [DateTime]
,CONVERT(varchar, DATEADD(ms, [Duration] * 1000, 0),108) as Duration
,[DialledNumber]
,[Originator]
FROM [SwitchData].[dbo].[CallData]
WHERE ([DateTime] BETWEEN @startdate AND @enddate) AND (rtrim(ltrim(Originator)) like @ExtNumber) OR
([DateTime] BETWEEN @startdate AND @enddate) AND (rtrim(ltrim(DialledNumber)) like @ExtNumber)
END
If you pass a null in @ExtNumber to that stored procedure, you will not get any results.
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 01/17/2013 12:31:25 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/17/2013 : 23:02:05
|
ALso if [DateTime] field has timepart the current condition [DateTime] BETWEEN @startdate AND @enddate would cause it to filter out any records that got created on @enddate after start of the day (12 midnight)
if you want to cover entire last day it should be
[DateTime] >= @startdate AND [DateTime] < @enddate +1
see
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
passing a null or blank to stored procedure
