All Forums Database
JoyC 2 posts Joined 09/15
29 Sep 2015
When does the value in a stored procedure variable change?

Hi -
Can someone help me understand if the values in the variable change when the values in the source changes?
 
Sample Tbl1 Data:
Fld1
1
 
Sample Tbl2 Data:
Fld1     Fld_X
1         ABC
2         XYZ
 
To explain it better, here is the sample SQL:
SELECT Fld1
INTO     :vFld1
FROM    Tbl1;
-- This will put 1 in vFld1 variable.
 
SELECT Fld_X
FROM    Tbl2
WHERE Fld1 = :vFld1;
-- This will return ABC
 
UPDATE Tbl1
SET Fld1 = 2;
 
SELECT Fld_X
FROM    Tbl2
WHERE Fld1 = :vFld1;
-- Will this statement return ABC or XYZ?
 
Thanks!

stevebarry 8 posts Joined 08/11
30 Sep 2015

Hi JoyC,
The last statement will return ABC because nowhere has the code changed the value of vFld1 from the first SELECT.
This can be easily verified by creating a simple stored procedure to test this.
Regards,
Steve.

JoyC 2 posts Joined 09/15
30 Sep 2015

Thanks a lot Steve. Unfortunately, I do not have access to a system where I can execute a sample SP, similar to the one above. Appreciate your help.
Cheers!

stevebarry 8 posts Joined 08/11
30 Sep 2015

No problem JoyC. If you can't persuade your DBA to grant you CREATE PROCEDURE privileges then Teradata also has a free edition -  Teradata Express for VMware - which you could install on your own machine for this kind of testing. More details can be found at:
http://downloads.teradata.com/download/database/teradata-express/vmware
and a user guide for installing version 14 can be found at
http://developer.teradata.com/database/articles/teradata-express-14-0-for-vmware-user-guide

You must sign in to leave a comment.