Q:

Must declare the scalar variable "@TEMPLATE_NAME"

I am having an error while I try to execute the following code,looked out for similar solutions but cant figure it out.
DECLARE @TAB_COL_NAME TABLE(idx int identity(1,1), COLUMNNAME VARCHAR(MAX))
DECLARE @TEMPLATE_NAME NVARCHAR(50)
SET @TEMPLATE_NAME = 'MYTABLE1'
INSERT INTO @TAB_COL_NAME EXEC('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '+@TEMPLATE_NAME)

Since I saw many saying to use sp_executesql, I have tried sp_executesql,
  DECLARE @SQL NVARCHAR(100)
  DECLARE @TEMPLATE_NAM NVARCHAR(50)
  SET @TEMPLATE_NAM = 'MYTABLE1'
  SET @SQL = N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '+ '@TEMPLATE_NAME'
 EXEC sp_executesql @SQL,
                   '@TEMPLATE_NAME Nvarchar(50)',
                    @TEMPLATE_NAME = @TEMPLATE_NAM

And this gives out the error :Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
What is the correct solution for this?
and can't I achieve this is in EXEC SQL?
some reply would be helpful.  

A:

Why are you trying to pass variables? In your case you are not bound to do so. Try the following using concatenation:

DECLARE @SQL NVARCHAR(100)
  DECLARE @TEMPLATE_NAME NVARCHAR(50)
  SET @TEMPLATE_NAME = 'mytable'
  SET @SQL = N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+ @TEMPLATE_NAME + ''''
  print @SQL
 EXEC sp_executesql @SQL

However, if you must parameterise, you can use sp_executesql in the following manner

 DECLARE @SQL NVARCHAR(100)
  DECLARE @TEMPLATE_NAME NVARCHAR(50)
  SET @TEMPLATE_NAME = 'mytable';
  SET @SQL = N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TEMPLATE_NAME';
 EXEC sp_executesql @SQL , N'@TEMPLATE_NAME NVARCHAR(50)',  @TEMPLATE_NAME

