how to use single quotations inside a transact sql statement

Just escape the quotes:

change

SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = '1' '

to

SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = ''1'' '

** Edit **

To include a local variable in the result, you could updated your query like this:

DECLARE @SQLQuery varchar(200)
DECLARE @tmpInt int

SET @tmpInt = 2
SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = ' + 
     convert(varchar, @tmpInt) + ' '

Use double ticks to escape them:

Declare @SQLQuery AS NVarchar(4000)
SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = ''1'' '
Execute (@SQLQuery)

If you want to use a local variable as you mention in your comment, you can do this:

Declare @SQLQuery AS NVarchar(4000)
Declare @Id AS NVarchar(3)

SET @Id = '1'
SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id= ''' + @Id  + ''''
Execute (@SQLQuery)

wrap the one single quote in to more like ''' and the tics will work as well.

Double the single quotes in the quote!

SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = ''1'' '