Mike,

I'm assuming that the invdate column is of datetime within the invoice table. If so, you should cast your variables as datetime before doing the comparison. Sql server is probably doing this automaticallly when you hard-code the values in the procedure, however when you are using character variables, it is having to do the conversion at runtime, so therefor it may be converting every rows invdate to a character field for evaluation.

Also for datetime fields, if you want to ensure that you are selecting all records for the last date specified, add 1 day to the EndDate variable and then use the less than operator, that way it will still pick up records with a time such as 23:59:59 + msecs. Those records would be ignored using the BETWEEN operator.

Create Procedure getInfo
   @Date 	 char(12)      as 

Declare 	@BegDate 	datetime,
   		@EndDate    datetime

SET @BegDate = CAST(@Date AS datetime),
SET @EndDate = DATEADD(dd,1,@BegDate)

--MustGo is a bit field

select soldto1, orderno, amtdue, mustgo
	from invoiceTable with (nolock)
	where mustgo = 1
      AND invdate >= @BegDate
      AND invdate < @EndDate

go

Hope this helps,
Kurt
>I have a stored procedure as follows...
>
>

>Create Procedure getInfo
>
>@Date 	 Char(20)
>
>as
>Declare 	@BegDate 	char(20),
>		@EndDate	char(20)
>
>Set	 @BegDate=rtrim(@Date) +' 00:00:00'
>Set	 @EndDate=rtrim(@Date) +' 23:59:59'
>
>--MustGo is a bit field
>
>select soldto1, orderno, amtdue, mustgo
>	from invoiceTable with (nolock)
>	where mustgo = 1 and invdate between @BegDate and @EndDate
>
>go
>

>
>This procedure takes approximately 35-45 seconds to run. But if I hardcode the dates in the where clause as '04/18/2005 00:00:00' and '04/18/2005 23:59:59' respectively it takes 1 second or less to run. Any ideas on why with the variable it takes longer and on how to fix it?