I'm having trouble inserting the results of a stored procedure into a table variable in Babelfish Aurora Postgres (AWS). Here's the code I'm using:
declare @temp TABLE (col1 int, col2 int, col3 int, col4 nvarchar(max))
insert into @temp (col1, col2, col3, col4)
EXEC [dbo].[test_proc]
select * FROM @temp
Definition of Stored Procedure:
create proc dbo.test_proc
as
begin
select 1 as col1, 1 AS col2, 24 as col3, 'sample value' as col4
end
I'm not sure what's going wrong. Can someone help me understand what might be the issue or suggest an alternative approach? Temporary table is also not working for same.
In v.3.4.0, this works fine provided you remove the schema name from the proc execution, e.g. EXEC [test_proc] . In earlier Babelfish releases this fails. The schema name should obviously be allowed to be specified and that is an outstanding issue - note that this applies only to INSERT..EXECUTE, not to regular proc executions.