Dynamic SQL String

I need to write a SQL String to retrieve a field value from a table. The problem is that I need to supply the table name as a parameter. If I was just updating a table, I could build a dynamic SQL String and use Exec()





This is what I would write if the name of the table was known:





Select @RecordNo = MAx([VehicleID]) from Alto





This is what my dynamic SQl string looks like:





Select @SqlStatement = 'Select Max([VehicleID]) from ' + @TableName





So how do I run this statement and get the value it would return? Is there an equivalent to exec() that returns a value?