passing a null or blank to stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

passing a null or blank to stored procedure

Author

Topic

silentbob
Starting Member

18 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-16 : 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 - 2013-01-16 : 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 - 2013-01-17 : 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)

7020 Posts

Posted - 2013-01-17 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources