One sp returns table into another sp

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

One sp returns table into another sp

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 12:47:29

When I'm checking the syntax in Query Analyzer for the following code, I get the error:

"Must declare the variable '@TempVariableTable' "

spGetTableTesting returns a table

CREATE PROCEDURE spGetTable
(@VALUE1 nvarchar(50),
@VALUE2 nvarchar(50))
AS
SET NOCOUNT ON
DECLARE @TempVariableTable table
(Field1 int)

EXEC @TempVariableTable = spGetTableTesting @VALUE1,@VALUE2
GO

How do I set the @TempVariableTable to the results from spGetTableTesting?

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-16 : 13:03:00

Try
Insert into @TempVariableTable (field1)
Exec spGetTableTesting

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 13:35:33

You actually can't use table variables when using INSERT with EXEC. You have to use a regular temporary table, the # kind.

CREATE TABLE #TempTable(...)

Insert into #TempTable (field1)
Exec spGetTableTesting

Tara Kizer
aka tduggan

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 13:52:30

Thanks, that worked.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-17 : 00:59:08

Also make sure that number of columns and datatypes of temporary tables match with that of sp's result

quote:
Insert into @TempVariableTable (field1)
Exec spGetTableTesting

You can't use table variable to insert data from output of sp. Only you can use tables,views,etc

Insert into @TempVariableTable (field1)
select field1 from otherTable

Madhivanan

Failing to plan is Planning to fail

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-18 : 00:19:39

Thanks for the advice again. While working with my current project, I ran into another issue that I ended up working around. The client software accessing the database is written in VB6. I'm using an ADO command object to execute the stored procedure command and return the result into a recordset object. The problem I had was that if I called a stored procedure from within VB and that stored proc subsequently created a tempororary table (with a # in front of it), then called another stored procedure that used the table, no recordset was returned.

I was able to run the command fine in Query Analyzer, so I'm wondering why I wasn't able to do this with the VB code.

Any ideas?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-18 : 02:01:13

the temp table created in the first Stored Procedure (SP) will be automatically dropped when the first SP exit. So the 2nd SP should get error when it is trying to select from the temp table. You should get the same result when running the 2 SPs in Query Analyser.

Try this simple SP to see the effect.

-- Create the first SP which will create a temp table and insert a record
create procedure sp1
as
begin
	create table #temp
	(
		col1	int,
		col2	int
	)
	insert into #temp select 1, 2
end
go
-- The 2nd SP will just select from the temp table
create procedure sp2
as
begin
	select * from #temp
end
go

-- Now execute the sp1 to create the temp table
exec sp1
-- sp2 wlll select the temp table
exec sp2
-- Will get error "Invalid object name '#temp'."

drop procedure sp1
drop procedure sp2

Temp table only exists within the scope of the SP. Global Temp Table (with ##) will exists within the same connection scope.
Try to run the scripts again by changing #temp to ##temp in both the SP (sp1 & sp2). You will not get any error.

----------------------------------
'KH'
Time is always against us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources