Dynamic sql queries in stored procedures

This is an old concept which I studied recently.The whole idea is to create a query in stored procedures and execute it.
Some times we cannot predict the table name or the criteria which should be used to retrieve or update the data in the sql query.Normally people constructs the query in the code itself and executes that query.But that exposes threat of SQL injection.
For example if we need to write a common method to select all the records from tables ie select * from <table name> we normally write a method with a signature which accepts the table as string and constructs the query and executes it.But there is another option which allows us to create the same query in the SP itself and execute the same.See the below sql.

Declare @SQL VarChar(1000)
declare @TableName varchar(50)

select @TableName = 'Employees'
SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

Hope the code is self explanatory and help you to avoid sql statements from your C# or VB code.