Or use a subquery

Select a.order_number, a.customer, qty_in_stock

From (

SELECT order_number, customer, stock_calc(<param> ) AS qty_in_stock 

FROM Orders) a

Where qty_in_stock  > 10

Aliases haven't been processed when the where clause executes, so they cause errors

Dunno about real tables, but that won't work with temp tables. The name in sysobjects in tempdb is not the same as the name you use to reference the table....

It will work.

That said, this kind of catch-all search query is very prone to poor execution plans due to parameter sniffing and cached plans. You may find that for certain...

You'll probably find the stack dump files in SQL's error log directory. (SQLDumpxxx.txt and SQLDumpxxx.mdmp)

I would recomend that you contact MS's Product Support and log a case for this. There...

Spid -2 is normally caused by orphened DTC transactions. I get them often from some JDBC queries. I can't think of any reason you should gt them from a select...

I do have a full time job, as does just about everyone else who answers questions here. I'll look at your stuff when I have a few minutes free

Have you considered indexed views to replace your derived tables? It will result in a slow down of inserts/updates but that trade off may be acceptable considering your reporting needs. 

Is session_id...

It is possible what you're trying to do, but the other way around. this works

CREATE PROCEDURE p1 AS

CREATE TABLE #Temp (...)

EXEC p2

GO

CREATE PROCEDURE p2 AS

SELECT * FROM #Temp

GO

It doesn't work...

Kimberley Tripp's blog and articles

http://www.sqlskills.com/blogs/kimberly/

http://www.sqlskills.com/articles.asp

Wow.

OK, I haven't read through all of that, maybe over lunch, but one thing caught my eye

IF

@GroupLevel