Can we store the result set of SP

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

Yes you are.

You need to create temp table & use it to store the return resultset of stored procedure.

CREATE TABLE #tempTable

(Col1 NVARCHAR(MAX),

Col2 NVARCHAR(MAX)

)

INSERT INTO #tempTable

EXEC procSample;

Hope sample will resolve your problem.

Thanks

garima.arya Say Hey Kid Points: 681 More actions · Answer 2

If you expect to select only one value for each column, I would recommed you to use OUT variables.

However, if there can be multiple values, then the above solution is correct except one thing, use table variable instead of temp table.

Thanks,

Garima

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Temp table is useful, if you want to supply the same table/data in the next calling procedure in the hierarchy.

Thanks

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 4

SSCarpal Tunnel

Points: 4558

August 23, 2010 at 12:39 am

THank u ...

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 5

Sorry for incon.

If i tried with this format not working

SELECT * INTO ##TEMP

FROM

T_SP_UPDATE_ITEM_DETAILS '139',30,'TB'

Error

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '139'.

please tell me

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 6

You cannot use 'select * into #temp from exec SPname'.

You will have to create the temp table first and then use insert statement with execute SP to add the result set of SP to temp table.

Thanks,

Amit kulkarni

garima.arya Say Hey Kid Points: 681 More actions · Answer 7

Instead of

SELECT * INTO ##TEMP

FROM

T_SP_UPDATE_ITEM_DETAILS '139',30,'TB'

write

SELECT * INTO ##TEMP

EXEC T_SP_UPDATE_ITEM_DETAILS '139',30,'TB'