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

Using SET statement in stored proc.

P: 31
Hi I am using the following statement in stored proc.
SET @A=@B+@C-@D
But it doesn't return any value...
how should I write the above statement...
Apr 25 '09 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,541
That seems fine to me. What values do you have in the other variables? Are any Null?
Apr 25 '09 #2

P: 31
The values in other variables are not NULL I have checked this By printing each and every value....
Apr 27 '09 #3

NeoPa
Expert Mod 15k+
P: 31,541
That's good, but it doesn't give me much to work with.

How about you set up a test Stored Procedure which uses that statement, but also PRINTs all the values. That way you can post in the Stored Procedure as well as the results, and I may be able to determine from that what is going amiss.
Apr 27 '09 #4

P: 31
The above three values i.e @B,@C,@D I m reading from a table...
@C & @D r fine but @B has a value 0 in it...so while reading I think so there is a problem and it does not calculate it...

So do u know how can I check if value in @B is 0....
I want to check after reading from table and not in select Query
Apr 28 '09 #5

NeoPa
Expert Mod 15k+
P: 31,541
Did you not understand my previous post?

I'll include it again, and if there's any part of it that you find confusing please explain and I'll see what I can do to clarify. Simply ignoring it will not allow us to progress.
@NeoPa
Apr 28 '09 #6

P: 31
Do u mean I should post the test proc. here
Apr 28 '09 #7

P: 31
The Test SP
Expand|Select|Wrap|Line Numbers
  1. Create Procedure TestPro
  2. @CUSTID AS CHAR(12)
  3. AS
  4. DECLARE @OPBALANCE AS NUMERIC(16,2)
  5. DECLARE @DEPOSIT AS NUMERIC(16,2)
  6. DECLARE @WITHDRAWAL AS NUMERIC(16,2)
  7. DECLARE @CLBALANCE AS NUMERIC(16,2)
  8.  
  9. SET @OPBALANCE=(SELECT OPBALANCE FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
  10. SET @DEPOSIT =(SELECT DEPOSIT FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
  11. SET @WITHDRAWAL=(SELECT WITHDRAWAL FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
  12.  
  13. SET @CLBALANCE=@OPBALANCE+@DEPOSIT-@WITHDRAWAL
  14. PRINT(@CLBALANCE)
Apr 28 '09 #8

P: 31
Now in the above SP the Opbalance field in my table is 0
But it may not be Zero all the time...
So after retreiving from table I wish to check if its 0 the assign 0 to @Opbalance bcoz it does not calaculate then...
Apr 28 '09 #9

NeoPa
Expert Mod 15k+
P: 31,541
@SnehaAgrawal
Yes. But from post #4 I need a version which PRINTs all the variables involved. I also need to see what the values produced are.

Your code should be something like :
Expand|Select|Wrap|Line Numbers
  1. Create Procedure TestPro
  2. @CUSTID AS CHAR(12)
  3. AS
  4. DECLARE @OPBALANCE AS NUMERIC(16,2)
  5. DECLARE @DEPOSIT AS NUMERIC(16,2)
  6. DECLARE @WITHDRAWAL AS NUMERIC(16,2)
  7. DECLARE @CLBALANCE AS NUMERIC(16,2)
  8.  
  9. SET @OPBALANCE = (SELECT OPBALANCE FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
  10. SET @DEPOSIT = (SELECT DEPOSIT FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
  11. SET @WITHDRAWAL = (SELECT WITHDRAWAL FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
  12.  
  13. PRINT @OPBALANCE
  14. PRINT @DEPOSIT
  15. PRINT @WITHDRAWAL
  16.  
  17. SET @CLBALANCE = @OPBALANCE + @DEPOSIT - @WITHDRAWAL
  18. PRINT @CLBALANCE
Apr 28 '09 #10

P: 31
One thing I forgot to mention tht I am using Dynamic sql the Queries are as as follows
Expand|Select|Wrap|Line Numbers
  1. SET @SQLSTRING=N'SELECT @DEPOSITOUT=DEPOSIT FROM '+@globalDBName+' ..GDayBalance WHERE CustID=@CustID'
  2.                 SET @PARAMDEFINITION=N'@CUSTID char(12),@DEPOSITOUT NUMERIC(16,2)  OUTPUT'
  3.                 SET @INTVARIABLE=@CUSTID
  4.                 EXECUTE SP_EXECUTESQL @SQLSTRING,@PARAMDEFINITION,@CUSTID=@INTVARIABLE,@DEPOSITOUT=@DEPOSIT OUTPUT
  5.  
  6.                 SET @SQLSTRING=N'SELECT @WithdrawalOUT=Withdrawal FROM '+@globalDBName+' ..GDayBalance WHERE CustID=@CustID'
  7.                 SET @PARAMDEFINITION=N'@CUSTID char(12),@WithdrawalOUT NUMERIC(16,2)  OUTPUT'
  8.                 SET @INTVARIABLE=@CUSTID
  9.                 EXECUTE SP_EXECUTESQL @SQLSTRING,@PARAMDEFINITION,@CUSTID=@INTVARIABLE,@WithdrawalOUT=@Withdrawal OUTPUT
  10.  
  11.                 SET @SQLSTRING=N'SELECT @OPBALANCEOUT=OPBALANCE  FROM '+@globalDBName+' ..GDayBalance WHERE CustID=@CustID'
  12.                 SET @PARAMDEFINITION=N'@CUSTID char(12),@OPBALANCEOUT NUMERIC(16,2)  OUTPUT'
  13.                 SET @INTVARIABLE=@CUSTID
  14.                 EXECUTE SP_EXECUTESQL @SQLSTRING,@PARAMDEFINITION,@CUSTID=@INTVARIABLE,@OPBALANCEOUT=@OPBALANCE OUTPUT
And u were asking as wht are the PRINTS VALUES
It returns nothing in Opbalance PRINT Output
It returns 2000 in Deposit PRINT Output
It returns 500 in Withdrawal PRINT Output
As it returns nothing in Opbalance it calculates nothing in CLBalance
and it prints nothing I mean no value
I m sorry if I m bothering u but its really imp. for me
The proc. very lenghty so I m not posting the whole proc.
Apr 29 '09 #11

ck9663
Expert 2.5K+
P: 2,878
I don't know why you need to create dynamic sql to get those values. Nevertheless, the only reason I can't think of why you don't have value is because your SELECT statement did not return any rows.

Try printing out your last sqlstring and paste it in your query analyzer and execute it.


---- CK
Apr 29 '09 #12

NeoPa
Expert Mod 15k+
P: 31,541
@SnehaAgrawal
I'm sure that's true, but we can treat it as irrelevant at this stage. Switching from one to another half way through is not good policy. Also, preparing a simpler version that shows the problem (as you did first) is a very good idea.
@SnehaAgrawal
Yes. It would be better to post them as shown (as this can give clues that you may miss when simply relaying them on), but I think this has told us enough to determine at least why it's losing the calculated value.

It seems that the code for setting @OPBALANCE is evaluating to Null. Null perpetuates in SQL arithmetic expressions, so if @OPBALANCE is Null, then @CLBALANCE will be also.
Apr 29 '09 #13

P: 31
Hi I got the solution u were right @Opbalance was NULL I just checked it using IF-Else it worked...thnks
May 1 '09 #14

NeoPa
Expert Mod 15k+
P: 31,541
Pleased to hear it :)

Good luck with your project.
May 1 '09 #15

Post your reply

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