bhuvana bhuvana - 1 year ago 81
SQL Question

Insert into table values from single variable SQL stored procedure

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.

Answer Source

Using Dynamic query you can achieve this ,In store 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download