0

I tend to use Temp Tables to pass large amounts of data from one stored procedure into another stored procedure. However, something that's always annoyed me is that, because it's implicitly passed/shared with the session, the sub stored procedure has no knowledge of it and constantly highlights table/column references as "Invalid Object".

Is there any way to "force" knowledge of a Temp Table's schema into a stored procedure?

--Yes, this is awful.  Quick and dirty pseudo code.
create procedure usp_myProc
(
    create table #temp
    (
        id int
        ,data int
    )

    insert into #temp(id, data)
    values(1, 1)

    exec usp_mySubProc;
)

create procedure usp_mySubProc
(
    --#temp and Data will both be highlighted as Invalid Objects
    --Because I'm updating the table, a Table Valued Parameter won't work
    while(1)
    (
        update
            #temp
        set
            Data = 1
    )
)
1

Is there any way to "force" knowledge of a Temp Table's schema into a Stored Proc?

You could pass the data using a Table-Valued Parameter instead. Or you could try something like this:

create proc usp_myProc
as
begin
    create table #temp
    (
        id int
        ,data int
    )

    exec usp_mySubProc;
end
go

create proc usp_mySubProc
as 
begin

    --table creation will be skipped at runtime
    if 1=0 create table #temp(id int, data int)

    update
        #temp 
    set
        data = 1

end
go
  • Unfortunately, as far as I know, table valued parameters are READONLY, which adds extra overhead if I need to manipulate the data. The second point seems to be creating the table within the stored proc, which means no data was passed in. I'll update my question shortly, I don't think I was clear with my ask. – Kalmino Nov 9 '17 at 14:38
  • 1
    "The second point seems to be creating the table" Look again. It only creates the table in the case that 1=0, which is only true for very small values of 1. So at runtime it will use the session temp table (or the temp table created a higher nested scope), but at design-time intellisense will see the local definition. – David Browne - Microsoft Nov 9 '17 at 14:46
  • Ahhhhh! I totally see it now! It will never actually create the table but you've tricked the proc into thinking that it could, which gives it the schema. It seems a little hack-ish but I love it. You've got my +1 and, barring any other answers/my quick test results, you'll get my answer checkmark in a day or so – Kalmino Nov 9 '17 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.