title: SQL Server insert into with dynamic parameters
body: <p>I'm trying to make a dynamic stored procedure for inserting records in tables where I pass in table name, schema name and insert values parameters.</p>
  
  <p>I've made it to select column names from table parameter and store it in a variable, but I can't manage how to make insert statement work.</p>
  
  <p>Please if anyone can help me.</p>
  
  <p>Here is my query:</p>
  
  <pre><code>DECLARE @TableName VARCHAR(100)
  DECLARE @SchemaName VARCHAR(10)
  DECLARE @Columns NVARCHAR(max)
  DECLARE @VALUES NVARCHAR(MAX)
  
  SET @SchemaName = 'dbo'
  SET @TableName = 'CLASSROOMS'
  SET @Columns = '';
  SET @VALUES = 'Classroom1,25'
  
  SELECT @Columns = @Columns + CAST(COLUMN_NAME AS varchar(50)) + ','
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName 
    AND TABLE_SCHEMA = @SchemaName 
    AND ORDINAL_POSITION &gt; 1
  
  SET @Columns = SUBSTRING(@Columns, 1, Len(@Columns) - 1)
  
  --SELECT @Columns
  
  DECLARE @SQL AS NVARCHAR(MAX)
  
  SET @SQL=N'INSERT INTO ' + @TableName + '(' + @Columns + ') VALUES (' + @VALUES + ')'
  
  EXEC sp_executesql @SQL
  </code></pre>
  
  <p>And here is <code>Classroom</code> table create script:</p>
  
  <pre><code>CREATE TABLE [dbo].[CLASSROOMS]
  (
      [ClassRoomID] [INT] IDENTITY(1,1) NOT NULL,
      [ClassRoomName] [NVARCHAR](50) NOT NULL,
      [MaxSits] [INT] NOT NULL,
  
      CONSTRAINT [PK_CLASSROOMS] 
          PRIMARY KEY CLUSTERED ([ClassRoomID] ASC)
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Thank you in advance</p>