using same table variable in Child Stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using same table variable in Child Stored proc

Author

Topic

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2008-02-05 : 02:03:46

Hi
I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs)
now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error

Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149
Must declare the table variable "@tmp_get_imu_retn".

Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.

Thanks,
BPG

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-05 : 02:12:03

This is an excerpt from Inside Microsoft SQL Server 2005: T-SQL Programming:
Scope and Visibility
The scope of a table variable is well defined. It is defined as the current level, and within it the current batch only, just as with any other variable. That is, a table variable is not accessible to inner levels, and not even to other batches within the same level. In short, you can use it only within the same batch it was created. This scope is much more limited than that of a local temporary table and is typically an important factor in choosing a temporary object type

Hope it gives you reason for error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 02:34:18

I think you need to use a global temp table (##table) for your purpose.

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2008-02-05 : 02:53:45

But as per our standard We can't use the temp table , Is there any way to use table variable in this issue.
Please advice.

Thanks,
BPG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 02:55:43

Can you explain your scenario in a bit more detail?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 03:00:02

Visakh, if you create a temp table #t in SP a and then make a call to SP b, temp table #t is visible in SP b.

CREATE PROC a
AS

CREATE TABLE #t (i INT)

INSERT #t
SELECT 1

EXEC b
GO

CREATE PROC b
AS

SELECT * FROM #t
GO

EXEC a
GO

DROP PROC a
DROP PROC b
GO

E 12°55'05.25"
N 56°04'39.16"

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2008-02-05 : 03:09:13

Currently i am using the Temp table in all the SP .the temp table is created in the main SP , which can be used in Child SP also for the calculation for other things.

Now we have to change the Temp table to Variable Table , but while changing to Variable Table , Child SP is not compling showing the error message

Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149
Must declare the table variable "@tmp_get_imu_retn".

Now i have to use the Table variable in all the SP , which is used in the main SP.

For example Main SP1 - A is used for declaration of table variable @table1
Now the @table1 should be used in all the child SPs say B,C and D.

Hope i am clear now.

thanks,
BPG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 03:20:50

Sunil told you why.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 03:30:46

quote:
Originally posted by Peso

Visakh, if you create a temp table #t in SP a and then make a call to SP b, temp table #t is visible in SP b.
CREATE PROC a
AS

CREATE TABLE #t (i INT)

INSERT #t
SELECT 1

EXEC b
GO

CREATE PROC b
AS

SELECT * FROM #t
GO

EXEC a
GO

DROP PROC a
DROP PROC b
GO

E 12°55'05.25"
N 56°04'39.16"