IF I do the following I can set up one where clause for the date selection

IF(@startdate IS NULL ) OR (@startdate = '')
SET @startdate = '1920-1-1 12:00:00'

-- I am pretty sure that they don't have records going back to the 1920's

IF(@enddate IS NULL ) OR (@enddate = '')
SET @enddate = GETDATE()

So this gives me all of the records from 1/1/1920 to today.

Or if the @enddates and @startdates have values it will run them.

So I can feed these into one select statement and create one #temptable regardless of what is in the input variable.

Last Problem.

How do I handle the where statement for @book, where the values range from
zero lenght string, null, 'ALL' or '20191'

Will a case statment work there?


Thanks
Jim










>>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.
>>
>
>See @@ROWCOUNT in BOL