Dynamic SQL error

Hello, Im trying to do dynamic SQL but get the following error with the SQL below

Must Declare the variable '@.TopRange'

can someone please help ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

Set @.TopRange = RTRIM(LEFT(REPLACE(@.param_leadage,'-',''),2))
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= @.TopRange AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= @.BottomRange'

EXEC(@.SQL)

In your code, you have added the variable into the string. When the string is executed, the variable does not exist.

I think that you want to add the variable's value to the string, so you would need to set it outside the quotes and concatenate the variable value to the string.

Something like this:

Code Snippet

DECLARE

@.TopRange varchar(8),
@.BottomRange varchar(8),

@.SQL nvarchar(1000)

SELECT

@.TopRange = RTRIM(LEFT(REPLACE(@.param_leadage,'-',''),2)),
@.BottomRange = LTRIM(RIGHT(REPLACE(@.param_leadage,'-',''),2)),

@.SQL = 'SELECT ' +

'tc.idStatus, ' +

'tc.idCustomer, ' +

'tc.DateSigned ' +
'FROM dbo.tblCustomer tc' +
'WHERE datediff( day, tc.DateSigned, getdate()) >= ' + @.TopRange +

' AND datediff( day, tc.DateSigned, getdate()) <= ' + @.BottomRange

EXECUTE ( @.SQL )

Note that I changed the datatypes of the Top/Bottom Range variables -that is so they will concatenate with the string without having to be cast or converted.

Also, using the datediff() function this way in the criteria of the query will negate any possibility of using indexing. The query will require at best an index scan, and at worst, an entire table scan.