simple problem with a table value function

[JnrJnr]

create function OutstandingBooks()
returns table
as
BEGIN 
 
declare       
        @TodaysDate date = getdate(),
        @CountStart int = 1,
        @CountRows int,
        @ReturnDate date,
        @Name varchar (50)
 
 
select @CountRows = count (rows) +1 from SignOut_TM
while (@CountStart <> @CountRows)
begin
select top (@CountStart) @ReturnDate = ReturnDate from SignOut_TM
                if (@TodaysDate > @ReturnDate)
                begin
                select @Name = EmployeName from SignOut_TM where ReturnDate = @ReturnDate
                end
return select EmployeeName,CourseName,BookName,TodaysDate where EmployeeName = @Name
    set @CountStart += 1            
end
END

I get the error - incorrect syntax near the word "begin"
what am I dong wrong?

[ck9663]

Here's the syntax for CREATE FUNCTION. Check some of the samples.

Also, you can not assign variable and assign a value at the same time.

Good Luck!!!

~~ CK

[JnrJnr]

ah thanx, but I got it right to what I wanted.
Instead of going through all the rows one by one (like I did) I just had to get all my "returnDates" that are smaller than my "TodaysDate" and return the required data like this

create function OutstandingBooks
()
returns @MyTable table
(
EmployeName varchar (50),
CourseName varchar (50),
BookName varchar (50),
TakenDate date
)
as
BEGIN 
 
declare       
        @TodaysDate date = getdate(),
        @CountStart int = 1,
        @ReturnDate date
 
insert @MyTable select EmployeName,CourseName,BookName,TakenDate from SignOut_TM where @TodaysDate > ReturnDate 
                set @CountStart += 1
 
return   
END
go
 
select * from dbo.OutstandingBooks()
go