SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 passing a null or blank to stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

silentbob
Starting Member

18 Posts

Posted - 01/16/2013 :  04:40:06  Show Profile  Reply with Quote
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
8765 Posts

Posted - 01/16/2013 :  05:21:59  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

silentbob
Starting Member

18 Posts

Posted - 01/16/2013 :  05:30:18  Show Profile  Reply with Quote
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



Go to Top of Page

silentbob
Starting Member

18 Posts

Posted - 01/17/2013 :  08:02:39  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/17/2013 :  12:30:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2013 :  23:02:05  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000