General information
Forum:
Microsoft SQL Server
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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only