Insert into table values from single variable SQL stored procedure

Question

Is there any way to capture the values to be inserted to a table into one variable as comma separated values and use the variable to execute insert into statement. For example, create a test table as

create table id_test (id int, name varchar(10))

declare a variable and set the value for the variable to be the values to be inserted into table with comma separated as shown below:

declare @test as varchar(60) ;
set @test = '10,''john''';

Now use the variable @test in insert into as

insert into id_test (id,name) values(@test)

Reason for asking this question is that, I do not want to pass all the variables from web page to a stored procedure. Instead I want to pass pass one variable from Web page which holds the comma separated values to be inserted into table.

The best method is to pass all variable's values as a separate parameter. Your method will lead to SQL Injection — Madhivanan
– Madhivanan, Commented Jul 20, 2016 at 10:04
@Madhivanan , is there any specific reason to do so? I thought, better to have one variable instead of passing individual variables as each table has more number of columns. — missingsemicolon
– missingsemicolon, Commented Jul 20, 2016 at 10:12
yes it leads to sql injection stackoverflow.com/questions/601300/what-is-sql-injection — Madhivanan
– Madhivanan, Commented Jul 20, 2016 at 10:31
Thanks Madhivanan.. I checked the link you gave. I will consider sql injects before finalizing solution! thanks again! — missingsemicolon
– missingsemicolon, Commented Jul 20, 2016 at 10:51

marc_s · Accepted Answer · 2016-10-01 07:32:50Z

2

Using a dynamic query you can achieve this. In stored procedure after variable declaration

declare @test as varchar(60); 
set @test = '10,''john''';   

declare @sql AS VARCHAR(MAX) = 'insert into id_test (id, name) values('+@test+')'

--USE EXEC() to execute your insert command
EXEC(@sql)

edited Oct 1, 2016 at 7:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 20, 2016 at 10:08

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TT. Over a year ago

Looks wide open to SQL Injection attacks.

Collectives™ on Stack Overflow

Insert into table values from single variable SQL stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related