>> Is there any other way that we can have our cake and eat it too?

Sorry. I can't think of anything.

-Mike


>>>I thought it was strange that I would encounter a similar sounding problem twice in the same day <bg> <
>
>hehehe ...yeah, well, we needed an answer quickly ... I posted this question in several different places.
>
>I've got another question for you though ... changing the sort order on our customer's server worked and it was fine for that one "emergency" situation, but since we can't control the sort order of TempDB for our customers, this was only a stop-gap solution. What we'll have to do now is go through all of our SP's and make sure that any reference to TempDB is all in lower case. OK, so if we want to get away from using Temp tables, you mentioned table valued functions (others have mentioned table variables ... which won't work because you can't pass them as parameters) ... we'd have a problem using a function because of the way we're using the temp tables. Let me explain further ... first, here's the code that I posted before:
>SP1 has this code:
>

>    CREATE TABLE #temp1 (xyz char(1))
>
>    EXEC SP2
>
>    SELECT * FROM #temp1
>

>Now, SP2 contains code that uses #temp1 if it exists:
>

>    IF OBJECT_ID('TempDb..#temp1) IS  NOT NULL
>        INSERT INTO #temp1 (xyz) VALUE ('x')
>    ELSE
>        SELECT xyz FROM someothertable
>

>
>The problem is that we simply can't use a table valued function in place of SP2 because SP2 needs to be called both ways ... either with #temp1 already defined or without it already defined. Is there any other way that we can have our cake and eat it too?
>
>
>~~Bonnie