Louis Davidson
Well, one thing you shouldn't be doing is using SELECT * in production code. Instead, explicitly name the columns. When tables change, you should make the changes to your code, as this is a far better way to maintain your code and expect things to go wrong on a table change, not just have them happen to you without your knowledge.
And yes, you are going to have to create the #hack table before running your query, and you will need to know the interface of the procedure's data set to do this. A trick to do this is to do:
SELECT * into tempdb..tableName FROM Invoice
and then script the table.
However, if you are just trying to get one row to work with in a different query, consider changing your procedure:
CREATE PROCEDURE tmp_weed
AS
SELECT
* FROM Invoice
WHERE InvoiceID
= 10007
GO
Into a function:
create function dbo.invoice$getFunction
(
@invoiceId int
)
returns table
as
return(
select invoiceId
from invoice
where invoiceId = @invoiceId)
go
Now, if you must use a temp table, you can use select into:
select invoiceId
into #hack
from dbo.invoice$getFunction(10007)
Or just use this function instead of the temp table:
select columnList
from table1
join dbo.invoice$getFunction(10007) as notAHack
on notAHack.invoiceId = table1.invoiceId