I was trying to make a complex program simple.
I made a view that Selected just the records I needed from
the huge transaction table. This made the joins to the smaller tables go much faster.
This is a warehouse program
The program is accessed by people or other programs. Nothing may be passed other than the customer code, optional parameters are start and end date,
book (part number) and type of transaction (Scrap, move, etc.)

So to make it simple I was selecting into #ASTRA_All the view and all of the joins.

I would then test for 'ALL', null, '', or '20961'
in the @BOOK variable.

if 'ALL', null, or '' I would select the entire #ASTRA_ALL into #ASTRA_BOOK
Or if a specific part number I would Select the rows with that part number into #ASTRA_BOOK. But of course you can not do that.

IF (@Book = '') OR (@Book IS NULL) OR (RTRIM(@Book) = 'ALL' )


BEGIN

SELECT *

INTO #ASTRA_BOOK

FROM #ASTRA_ALL



END

ELSE

BEGIN

SELECT *

INTO #ASTRA_BOOK

FROM #ASTRA_ALL

WHERE [Primary Reference] = RTRIM(@Book)

END

Can I SELECT INTO #ASTRA_ALL_BOOK or SELECT INTO #ASTRA_ONE_BOOK and then test to see which one has data and SELECT that one into #ASTRA_BOOK?

Thank you
Jim






>>When I run the procedure I get:
>>There is already an object named '#ASTRA_BOOK' in the database.
>>When i type in DROP TABLE #ASTRA_BOOK I get:
>>
>>Cannot drop the table '#ASTRA_BOOK', because it does not exist in the system catalog.
>
>You cannot have 'CREATE TABLE #ASTRA_BOOK' twice in the same stored procedure even if only one is conditionally executed.