Using SET statement in stored proc.

[SnehaAgrawal]

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...

[NeoPa]

That seems fine to me. What values do you have in the other variables? Are any Null?

[SnehaAgrawal]

The values in other variables are not NULL I have checked this By printing each and every value....

[NeoPa]

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.

[SnehaAgrawal]

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

[NeoPa]

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.

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.

[SnehaAgrawal]

Do u mean I should post the test proc. here

[SnehaAgrawal]

The Test SP

Create Procedure TestPro
@CUSTID AS CHAR(12)
AS
DECLARE @OPBALANCE AS NUMERIC(16,2)
DECLARE @DEPOSIT AS NUMERIC(16,2)
DECLARE @WITHDRAWAL AS NUMERIC(16,2)
DECLARE @CLBALANCE AS NUMERIC(16,2)

SET @OPBALANCE=(SELECT OPBALANCE FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
SET @DEPOSIT =(SELECT DEPOSIT FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
SET @WITHDRAWAL=(SELECT WITHDRAWAL FROM GDAYBALANCE WHERE CUSTID=@CUSTID)

SET @CLBALANCE=@OPBALANCE+@DEPOSIT-@WITHDRAWAL
PRINT(@CLBALANCE)

[SnehaAgrawal]

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...

[NeoPa]

Do u mean I should post the test proc. here

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 :

Create Procedure TestPro
@CUSTID AS CHAR(12)
AS
DECLARE @OPBALANCE AS NUMERIC(16,2)
DECLARE @DEPOSIT AS NUMERIC(16,2)
DECLARE @WITHDRAWAL AS NUMERIC(16,2)
DECLARE @CLBALANCE AS NUMERIC(16,2)

SET @OPBALANCE = (SELECT OPBALANCE FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
SET @DEPOSIT = (SELECT DEPOSIT FROM GDAYBALANCE WHERE CUSTID=@CUSTID)
SET @WITHDRAWAL = (SELECT WITHDRAWAL FROM GDAYBALANCE WHERE CUSTID=@CUSTID)

PRINT @OPBALANCE
PRINT @DEPOSIT
PRINT @WITHDRAWAL

SET @CLBALANCE = @OPBALANCE + @DEPOSIT - @WITHDRAWAL
PRINT @CLBALANCE

[SnehaAgrawal]

One thing I forgot to mention tht I am using Dynamic sql the Queries are as as follows

SET @SQLSTRING=N'SELECT @DEPOSITOUT=DEPOSIT FROM '+@globalDBName+' ..GDayBalance WHERE CustID=@CustID'
				SET @PARAMDEFINITION=N'@CUSTID char(12),@DEPOSITOUT NUMERIC(16,2)  OUTPUT'
				SET @INTVARIABLE=@CUSTID
				EXECUTE SP_EXECUTESQL @SQLSTRING,@PARAMDEFINITION,@CUSTID=@INTVARIABLE,@DEPOSITOUT=@DEPOSIT OUTPUT

				SET @SQLSTRING=N'SELECT @WithdrawalOUT=Withdrawal FROM '+@globalDBName+' ..GDayBalance WHERE CustID=@CustID'
				SET @PARAMDEFINITION=N'@CUSTID char(12),@WithdrawalOUT NUMERIC(16,2)  OUTPUT'
				SET @INTVARIABLE=@CUSTID
				EXECUTE SP_EXECUTESQL @SQLSTRING,@PARAMDEFINITION,@CUSTID=@INTVARIABLE,@WithdrawalOUT=@Withdrawal OUTPUT

				SET @SQLSTRING=N'SELECT @OPBALANCEOUT=OPBALANCE  FROM '+@globalDBName+' ..GDayBalance WHERE CustID=@CustID'
				SET @PARAMDEFINITION=N'@CUSTID char(12),@OPBALANCEOUT NUMERIC(16,2)  OUTPUT'
				SET @INTVARIABLE=@CUSTID
				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.

[ck9663]

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

[NeoPa]

One thing I forgot to mention tht I am using Dynamic sql the Queries are as as follows ...

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.

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.

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.

[SnehaAgrawal]

Hi I got the solution u were right @Opbalance was NULL I just checked it using IF-Else it worked...thnks

[NeoPa]

Pleased to hear it :)

Good luck with your project.