Need help with date parameter in stored procedure.

[spiderusa]

Hi I'm using this stored proc in crystal reports and need help with it. The trouble is with the dates. I'm not getting any errors but the results are not right. I tried to comment out one of the dates and check the other one.
Start date - irrespective of the date entered, the amt is summed to date i.e. the total amt available in the database.
End date - irrespective of the date entered a null value is returned.
The Customer and Code parameters are ok.
The customer Id and code parameters are multiple values that will be choosen by the user. I used dynamic sql to include them in the stored proc. The date values had to be varchar since the whole statement is varchar.

Thanks for your time.

CREATE PROCEDURE dbo.Summed_amount

@CustomerID varchar(254)
, @StartDate varchar
, @EndDate varchar
, @Code varchar(500)

AS

BEGIN
	SET NOCOUNT ON

	DECLARE @SQL varchar(1000)
		
Select @SQL = 
	'Select sum(returned) as Amount
	from customer_account'  
	


Select @SQL = @SQL + ' where' +
	--' Customer_id in (' + @CustomerID + ')' +
	--' change_date >= ' + @StartDate 
	' change_date <= ' + @EndDate 
	--' and update_code in (' + @Code +')'
	 
	
EXEC (@SQL)
END
GO

 

[SQLDenis]

this ( ' change_date <= ' + @EndDate
) needs to be
'AND change_date <= ' + @EndDate


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[spiderusa]

I removed the 'and' because I was commenting the other parameters and checking for just the end date.

the original code was

Select @SQL = @SQL + ' where' +
    ' Customer_id in (' + @CustomerID + ')' +
    ' and change_date >= ' + @StartDate 
    ' and change_date <= ' + @EndDate 
    ' and update_code in (' + @Code +')'

 

[SQLDenis]

try putting quotes around the dates and make sure that they are in the right format

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/