Skip to content
Advertisement

How to use an IF statement within a view to drop/use a #Temp Table?

I need to create a view, which begins with the following SQL:

IF OBJECT_ID('tempdb..#TempTable') Is Not null
Drop Table #TempTable

I am also rebuilding this #TempTable further on in the view, and then using data in the Temptable for the rest of the query – for better performance. See:

Reduce cost for Table Valued Function – XML Reader in query plan – how?

However, SSMS is telling me:

Incorrect syntax near the keyword 'IF'
Views or functions are not allowed on temporary tables. 
Table names that begin with '#' denote temporary tables.

Is there any way to use the IF statement, drop the Temptable, and then use the rebuilt Temptable in the view?

ANSWER – I need to use a stored procedure, not a view.

Advertisement

Answer

A stored procedure is quite different from a view. A view is simply an encapsulated query.

However, a stored procedure cannot be used in a SELECT statement. You can only execute it using EXEC.

What you probably want is a user-defined function (UDF). UDFs return a table, so they can be referenced in the FROM clause of a query. Unlike a view, they can contain multiple statements and can have parameters.

As a note, there is no need to delete temporary tables in stored procedures or functions. If the temporary table is declared in the body of the code, then it is automatically deleted when the code is exited. However, you might want to use a table variable instead.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement