SQL Server insert into with dynamic parameters

Question

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.

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.

Please if anyone can help me.

Here is my query:

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 > 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

And here is Classroom table create script:

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]

Thank you in advance

Answer 1

Try this:

SET @VALUES='''Classroom1'',25'

The [ClassRoomName] is a nvarchar field.

• user3782230: Yes, That's it, Thank you very much.. When a men is frustrated can't see that :) Thank's Again
• MiguelH: @bruno beat me by seconds! I normally comment out the EXEC statement and do a Select @SQL instead. I can then copy and paste this to a new query and run it to check if it works! Makes debugging a lot easier!
• user3782230: @bruno.almeida What about some example for dynamic update?
• bruno.almeida: Well, first of all i strongly advice you to not do inserts or updates that way. It is very error prone and can create security flaws. If i can, i always use Entity Framework (but exists many others).That said, what is your concern about dynamic update? But is better open a new question with it, instead of talking about other off-topic issues.