output parameters are not returning the values

[raghurocks]

this is my prcoedure...

ALTER PROCEDURE test
@fromdate  as varchar(50),
@todate    as varchar(50),
@mailbox   as varchar(25),
@open      as int output,
@close     as int output,
@queue     as int output
 
 
AS
BEGIN  
   IF(@mailbox='HelpDeskOSA')
   BEGIN
                   select @open=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
                                                     and cast(@fromdate as datetime)< DateInitiated
                                                     and cast(@todate as datetime)> DateInitiated
 
                   select @close=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox                                                     
                                                     and [Status] in ('complete','canceled')
                                                     and [StatusDate] between cast(@fromdate as datetime)
                                                     and cast(@todate as datetime)
 
                   select @queue=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
                                                     and [Status] not in ('complete','canceled')  
                                                     and cast(@fromdate as datetime)< DateInitiated 
                                                     and cast(@todate as datetime)> DateInitiated
 
                                                     END
 
   IF(@mailbox='[e-mail]')
   BEGIN
                   select @open=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
                                                     and cast(@fromdate as datetime)< DateInitiated
                                                     and cast(@todate as datetime)> DateInitiated
 
                   select @close=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox                                                     
                                                     and [Status] in ('complete','canceled')
                                                     and [StatusDate] between cast(@fromdate as datetime)
                                                     and cast(@todate as datetime)
 
                   select @queue=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
                                                     and [Status] not in ('complete','canceled')  
                                                     and cast(@fromdate as datetime)< DateInitiated 
                                                     and cast(@todate as datetime)> DateInitiated 
                                                     END
 
   IF(@mailbox='All')
   BEGIN
 
                   select @open=count(*) from esinet_dbo.HelpDesk1 where Mailbox in ('HelpDeskOSA','[e-mail]')
                                                     and cast(@fromdate as datetime)< DateInitiated
                                                     and cast(@todate as datetime)> DateInitiated
 
                   select @close=count(*) from esinet_dbo.HelpDesk1 where Mailbox in ('HelpDeskOSA','[e-mail]')                                                   
                                                     and [Status] in ('complete','canceled')
                                                     and [StatusDate] between cast(@fromdate as datetime)
                                                     and cast(@todate as datetime)
 
                   select @queue=count(*) from esinet_dbo.HelpDesk1 where Mailbox in ('HelpDeskOSA','[e-mail]')
                                                     and [Status] not in ('complete','canceled')  
                                                     and cast(@fromdate as datetime)< DateInitiated 
                                                     and cast(@todate as datetime)> DateInitiated 
                                                     END
 
return @open 
return @close
return @queue
 
 
 
 
END

thanx in advance.......

[Rabbit]

Please use code tags when posting code.

You need to be detailed about your problem. You just have your stored procedure code. That by itself does not return anything. You haven't shown how you're trying to execute it and how you're trying to pull the return values.

[raghurocks]

Thanx for the reply Mr.Rabbit...I am using 3 out parameters in the procedure..which should return the values based on the mailbox option...how to return the output parameters values?

[Rabbit]

You still haven't shown me the code that actually runs the stored procedure. That's where you need to make changes to get it to return the output parameters. I can't advise you on how to fix it if you don't show me how you call it.

[raghurocks]

actually still i am executing the stored procedure by giving proper values...but its returning null...when i run the queries individually in the procedure then i get the values...why the output parameters are not carrying/displaying the actual values..where the queries can...

[raghurocks]

what i want is just tell me is there anything wrong in my procedure....

[Rabbit]

I don't see anything wrong with the procedure. As long as your queries are correct. What's most likely wrong is that you're calling it incorrectly. But since you haven't shown how you're calling it, I can't tell you if that's the case.

[ck9663]

If you are expecting those three returns to be executed, you're probably wrong. The first return will be executed with the variable @open as returned value. Anything after the first return is not executed.

That is not the way you pass the value of the returned variables. It will also depend on the way you call the SP, which you seems unable to share.

We cannot help you fully if there are missing information from your question.

Good Luck!!!


~~ CK