Using Local Variables In A Stored Procedure

[gderosa]

Hey guys I need some help with local variables, here is my current code:

CREATE PROCEDURE CreateFile
 
 AS  
DECLARE @notification int
SET @notification = (SELECT NotificationPeriod FROM Notification Where NotificationID = 1)
SET NOCOUNT ON
 
--Populate template
EXEC sp_makewebtask 
 
@outputfile = 'C:\xpsmtp\Test1.html',
@query = 'SELECT ContractSequenceNumber, ContractName, Vendor, OrigValue, CONVERT(VARCHAR, StartDate), CONVERT(VARCHAR, EndDate) FROM Contracts WHERE  EndDate >= CURRENT_TIMESTAMP AND EndDate <= CURRENT_TIMESTAMP + @notification',
@templatefile = 'C:\xpsmtp\Test1.tpl',@dbname = ContractMgt, @whentype = 1
GO

Now with the @notification local variable, it is being set to a value with the first select statement, this value is either 30, 60, or 90 and will be changed by the user periodically. Now the the procedure has this variable set to a value from pulling it in from a table, I want it to be used in the second query which is @query. It is supposed to select records between todays date and 30, 60, or 90 days from now, which would be where the @notification comes into play. When I try to execute this procedure I get this error:

Must declare the variable '@notification' .

Any ideas why it is not working? When I hardcode a a value in place of @notification (30, 60, or 90) the query works perfectly. Thanks very much in advance!

[iburyak]

@notification can not be inside your quoted string in this case it is not used as a variable.
See my small correction below

[PHP]
@query = 'SELECT ContractSequenc eNumber, ContractName, Vendor, OrigValue, CONVERT(VARCHAR , StartDate),
CONVERT(VARCHAR , EndDate)
FROM Contracts WHERE EndDate >= CURRENT_TIMESTA MP
AND EndDate <= CURRENT_TIMESTA MP + ' + @notification[/PHP]

[gderosa]

Thanks very much, it works!