Umbraco + Petapoco - Stored procedure to temp table

Question

Evening,

I'm trying to exec a stored procedure into a temporary table so I can join some bits from another table.

Obviously the ideal solution would be to add the join into the stored procedure however this is currently not an option.

Here's what I'm trying to run in my controller action:

db.Query<HomeLatestTradesViewModel>(Sql.Builder
.Append("INSERT INTO #tmp_trade")
.Append("EXEC get_trade_data_latest")
 );

Which hits me with:

Invalid object name 'HomeLatestTradesViewModel'.

I believe this is because I need a ; before the EXEC however that just throws an error telling me it shouldn't be there!

Any help getting this to work is appreciated.

Answer 1

If you want to execute (not retrieve data), you should use

db.Execute(Sql.Builder
.Append(";INSERT INTO #tmp_trade")
.Append("EXEC get_trade_data_latest")
 );

(but I guess this wont work, depends on how have you create the temp table, and if you are in a transaction)

If you want to fiddle with temp data, you are better getting the data into a list, and then playing with it

var templist = db.Query<HomeLatestTradesViewModel>(Sql.Builder
.Append("EXEC get_trade_data_latest")
 );