>Hi everyone,
>I am writing SP and getting a error message for logic like this:
>
>IF EXISTS (SELECT *FROM [tempdb].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#MainView___%') DROP TABLE #MainView
>
>IF @State = ''
> SELECT * INTO #MainView FROM main WHERE CONVERT(varchar, date, 101)=@Date
>ELSE
> SELECT * INTO #MainView FROM main WHERE CONVERT(varchar, date, 101)=@Date AND State=@State
>
>It says that object #MainView already exists in database
>What do I do wrong?
>Thanks in advance

The compiler doesn't differentiate between the two parts of the IF..ELSE, so it has presumed the creation of the temp table in the IF before trying to compile the ELSE where it assumes another table creation, hence the error.

Prior to the IF..ELSE, you can either:
- Create the temp table using CREATE TABLE... (since you probably already know the structure)
- If you don't necessarily know (or need to know) the structure, you can SELECT TOP 0 * INTO... the temp table.

and then in the IF, do an INSERT INTO the temp table

FWIW: You'll typically get better performance with a CREATE TABLE, because some sys tables get locked with SELECT INTO.