stored procedure help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

stored procedure help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-31 : 03:26:19

I have an sp

CREATE PROC sptats
@fromdate datetime,
@todate datetime,
@customerid varchar
AS
set nocount off
declare
@total bigint

select @total=count(id) from billing where (date>=@fromdate and date<=@todate)

return
set nocount on
GO

now it is possible that the @fromdate will be = to "" -- if it is then i don't want that as part of the clause
now if customerid is not = "" then I want to add on to the where clause where customerid=@customerid

basically it could be passed either a date range or customer id OR both

how can I do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 03:28:35

don't use '' and pass into the datatime variable. use NULL instead


select @total=count(id) 
from   billing 
where  date >= coalesce(@fromdate, date)
and    date <= coalesce(@todate, date)

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-31 : 03:38:44

so if date is null then it will return all dates?

how do i add that if the customerid is not null it adds to the where just where customerid=@customerid

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 03:47:55

Yes

where customerid = coalesce(@customerid, customerid)

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-31 : 04:01:46

so i did in the sp

select @total=count(id) from billing where Duration>0 and (date >= coalesce(@fromdate, date) and date <= coalesce(@todate, date)) and customerid = coalesce(@customerid, customerid)

but when i run it with a null date and a customerid that i know exists - it returns no records

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources