By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,255 Members | 1,701 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,255 IT Pros & Developers. It's quick & easy.

output parameters are not returning the values

P: 46
this is my prcoedure...


Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE test
  2. @fromdate  as varchar(50),
  3. @todate    as varchar(50),
  4. @mailbox   as varchar(25),
  5. @open      as int output,
  6. @close     as int output,
  7. @queue     as int output
  8.  
  9.  
  10. AS
  11. BEGIN  
  12.    IF(@mailbox='HelpDeskOSA')
  13.    BEGIN
  14.                    select @open=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
  15.                                                      and cast(@fromdate as datetime)< DateInitiated
  16.                                                      and cast(@todate as datetime)> DateInitiated
  17.  
  18.                    select @close=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox                                                     
  19.                                                      and [Status] in ('complete','canceled')
  20.                                                      and [StatusDate] between cast(@fromdate as datetime)
  21.                                                      and cast(@todate as datetime)
  22.  
  23.                    select @queue=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
  24.                                                      and [Status] not in ('complete','canceled')  
  25.                                                      and cast(@fromdate as datetime)< DateInitiated 
  26.                                                      and cast(@todate as datetime)> DateInitiated
  27.  
  28.                                                      END
  29.  
  30.    IF(@mailbox='HelpWebJJ@esi-intl.com')
  31.    BEGIN
  32.                    select @open=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
  33.                                                      and cast(@fromdate as datetime)< DateInitiated
  34.                                                      and cast(@todate as datetime)> DateInitiated
  35.  
  36.                    select @close=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox                                                     
  37.                                                      and [Status] in ('complete','canceled')
  38.                                                      and [StatusDate] between cast(@fromdate as datetime)
  39.                                                      and cast(@todate as datetime)
  40.  
  41.                    select @queue=count(*) from esinet_dbo.HelpDesk1 where Mailbox=@mailbox 
  42.                                                      and [Status] not in ('complete','canceled')  
  43.                                                      and cast(@fromdate as datetime)< DateInitiated 
  44.                                                      and cast(@todate as datetime)> DateInitiated 
  45.                                                      END
  46.  
  47.    IF(@mailbox='All')
  48.    BEGIN
  49.  
  50.                    select @open=count(*) from esinet_dbo.HelpDesk1 where Mailbox in ('HelpDeskOSA','HelpWebJJ@esi-intl.com')
  51.                                                      and cast(@fromdate as datetime)< DateInitiated
  52.                                                      and cast(@todate as datetime)> DateInitiated
  53.  
  54.                    select @close=count(*) from esinet_dbo.HelpDesk1 where Mailbox in ('HelpDeskOSA','HelpWebJJ@esi-intl.com')                                                   
  55.                                                      and [Status] in ('complete','canceled')
  56.                                                      and [StatusDate] between cast(@fromdate as datetime)
  57.                                                      and cast(@todate as datetime)
  58.  
  59.                    select @queue=count(*) from esinet_dbo.HelpDesk1 where Mailbox in ('HelpDeskOSA','HelpWebJJ@esi-intl.com')
  60.                                                      and [Status] not in ('complete','canceled')  
  61.                                                      and cast(@fromdate as datetime)< DateInitiated 
  62.                                                      and cast(@todate as datetime)> DateInitiated 
  63.                                                      END
  64.  
  65. return @open 
  66. return @close
  67. return @queue
  68.  
  69.  
  70.  
  71.  
  72. END

thanx in advance.......
Aug 7 '12 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Aug 7 '12 #2

P: 46
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?
Aug 8 '12 #3

Rabbit
Expert Mod 10K+
P: 12,430
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.
Aug 8 '12 #4

P: 46
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...
Aug 9 '12 #5

P: 46
what i want is just tell me is there anything wrong in my procedure....
Aug 9 '12 #6

Rabbit
Expert Mod 10K+
P: 12,430
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.
Aug 9 '12 #7

ck9663
Expert 2.5K+
P: 2,878
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
Aug 9 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.