What is wrong with this simple SQL? Fails when put in a stored procedure works otherwise

When I ran as is, it works perfectly fine but putting this exact code into a stored procedure in SQL 2005 fails.

I get this error

Msg 102, Level 15, State 1, Procedure GetCurrentLoadDate开发者_如何学运维, Line 23. Incorrect syntax near '@vardate'.

What is wrong with this call that it can work as a declaration and return the result set but fail if put in stored procedures?

declare @date datetime
declare @vardate varchar(10)

set @date = getDate()

set @vardate = CONVERT(varchar(10), @date ,101)

select tableloaded, insertdatetime, sourcesystemdatetime, FriendlyDescription
from dbo.tbl_loadSourcedates_dttm
where CONVERT(varchar(10), insertdatetime, 101) = @vardate

Thanks Dhiren

You are probably missing the END at the end of the stored proc definition that you neglected to show us. I get the same error If I append

create proc foo
as
begin

to the beginning of your posted code.

Do you have exactly this?

CREATE PROCEDURE GetCurrentLoadDate

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @date datetime

declare @vardate varchar(10)

set @date=getDate()

set @vardate=CONVERT(varchar(10), @date ,101)

select tableloaded,insertdatetime,sourcesystemdatetime,FriendlyDescription

from dbo.tbl_loadSourcedates_dttm

where CONVERT(varchar(10), insertdatetime ,101)=@vardate
END
GO

Because it looks to me that you are missing something when you declare your proc since you are getting a syntax exception on the very first line.