execute procedure if not null or zero

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)

execute procedure if not null or zero

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-08-11 : 08:52:13

Hi
How can I execute procedure 2 if procedure 1 returns a integer that is more then 0?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 08:54:39

[code]
exec @stat = proc1
if @stat > 0 exec proc2
[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-08-11 : 09:09:45

Why doesn't this work..


Declare @Qty Int
SELECT  @Qty = exec p_CheckAndCountTodaysOrders @FromDate=GetDate(), @ToDate=GetDate()
if @Qty > 0 exec sp_SMTPMail @SenderName='John Wayne'

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-11 : 10:29:10

Try this:

Declare @Qty Int
Declare @Now datetime
set @Now = Getdate()
exec @Qty = p_CheckAndCountTodaysOrders @FromDate=@Now, @ToDate=@Now
if @Qty > 0 exec sp_SMTPMail @SenderName='John Wayne'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-11 : 20:04:55

IN RE: "Why doesn't this work.."

It doesn't work because, syntactically, you can't invoke a stored procedure from the SELECT clause. If you changed the stored procedure into a function then you'd be okay.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 21:38:21

quote:

Originally posted by magmo

Why doesn't this work..


Declare @Qty Int
SELECT  @Qty = exec p_CheckAndCountTodaysOrders @FromDate=GetDate(), @ToDate=GetDate()
if @Qty > 0 exec sp_SMTPMail @SenderName='John Wayne'

1. You can't pass in function return value to a Stored Procedure directly. You have to go via a variable


declare @FromDate datetime,
        @ToDate   datetime

select  @FromDate = getdate(),
        @ToDate   = getdate()

Declare @Qty Int
SELECT  @Qty = exec p_CheckAndCountTodaysOrders @FromDate=FromDate, @ToDate=@ToDate
if @Qty > 0 exec sp_SMTPMail @SenderName='John Wayne'

2. getdate() will return date and time. If your FromDate and ToDate is both same and equal to current date & time, i doubt you will get any result back.

this will give you the current date with time at 00:00:00
select @FromDate = dateadd(day, datediff(day, 0, getdate()), 0),
@ToDate = dateadd(day, datediff(day, 0, getdate()), 0)

If your date column for comparision does not contain time, you can just
where datecol >= @FromDate and datecol <= @ToDate

if it does contain the time, you will need to use >= today and < tomorrow

where datecol >= @FromDate and datecol <= dateadd(day, 1, @ToDate)

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-08-12 : 03:26:08

Thank you all for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources