Dynamic SQL Procedure

I Have created a procedure which is:

CREATE PROCEDURE test.table_creation ( @ID INT ) AS BEGIN

    DECLARE @SQL VARCHAR(1000)
    DECLARE @SchemaName VARCHAR(50)
    DECLARE @TableName VARCHAR(100)

    SELECT  @SQL = 'Create Table ' + @SchemaName + '.' + @TableName + '('
    SELECT  @SQL = @SQL + 'ID int NOT NULL Primary Key, Name VarChar(10))'

    EXEC (@SQL)

END

The problem here is I have to get the table name and Schema name from another table called sample. The query to get those is:

SELECT 开发者_如何学GoSource_Schema,Source_Table FROM sample where ID = 12

How do i use these values in the above procedure.

---

It seems a dubious requirement (you might want to read The Curse and Blessings of Dynamic SQL) but

CREATE PROCEDURE test.table_creation (@ID INT)
AS
  BEGIN 
      DECLARE @SQL NVARCHAR(1000) /*Use nvarchar*/
      DECLARE @SchemaName sysname /*Use sysname*/
      DECLARE @TableName sysname

      SELECT @SchemaName = Source_Schema,
             @TableName = Source_Table
      FROM   sample
      where  ID = @ID


       /*Use QUOTENAME*/
      SELECT @SQL = 'Create Table ' + QUOTENAME(@SchemaName) + '.' +
                    QUOTENAME(@TableName) +
                           '(ID int NOT NULL Primary Key, Name VarChar(10))'

      EXEC (@SQL)
  END  

---

SELECT 
   @SchemaName = Source_Schema, 
   @TableName = Source_Table
FROM sample where ID = 12