When does the value in a stored procedure variable change?

Database
Fan

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!

3 REPLIES
Enthusiast

Re: When does the value in a stored procedure variable change?

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.

Fan

Re: When does the value in a stored procedure variable change?

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!

Enthusiast

Re: When does the value in a stored procedure variable change?

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