How to insert values into a table var from an SP

[RRinTetons]

I use temp tables to accomplish this, but I'd like to know how to do it with table vars.

I can create a temp table and call an existing sp to put data into it. The sp returns a selection that populates the temp table. The syntax for that is like:

CREATE TABLE #MyTempTable
<table def>

INSERT #MyTempTable
SELECT dbo.MySP(var1, var2)

and all is well

But I wanted to do it as

DECLARE @MyTempTable TABLE
<table def>

then the same INSERT doesn't work.

How do I populate the TABLE var in this case? Is it some sort of an assignment statement?

-
Richard Ray
Jackson Hole Mountain Resort

 

[RiverGuy]

The syntax is as follows:

INSERT INTO @MyTempTable 
EXEC MyStoredProc {Parameters}

 

[RRinTetons]

I gives back an error -

'EXECUTE cannot be used as a source when inserting into a table variable. Number:197 Severity:15 State:1'.

-
Richard Ray
Jackson Hole Mountain Resort

 

[RiverGuy]

Bummer, it looks like it may only be valid in SQL 2005+.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2401512&SiteID=1

Any reason why you can't just use the temp table?

 

[RRinTetons]

Nope, the temp table will work fine, I just wanted to start using table vars since I'm not familiar with them. Now I know a useful thing about them . This app is moving onto SQL Server 2005 in 2 weeks, I'll try it again the next time I have a similar need.

-
Richard Ray
Jackson Hole Mountain Resort

 

[ArunPrithviraj]

Hi tons,

Can you use a function instead of procedure?
If that is the case, try out the following code and correct accordingly it would work for u...

DECLARE @TAB AS TABLE
(ID INT,
TEXTDATE NVARCHAR(100))

//calling function to get the values to be inserted into tablevariables
INSERT INTO @TAB
SELECT * from dbo.TEST()

SELECT * FROM @TAB

//Function which u are calling above
CREATE function TEST()
RETURNS TABLE AS
RETURN (select 20 as ID,'test' as textresult)

Thanks,
AP