March 22, 2010 at 1:53 am
Hi,
I've requirement where user can enter multiple columns like first name, lastname, email, age, sex fields and insert them into database.
on my frontend aspx page, i'll be showing a single row initially with 5 text boxes(first name, lastname, email, age, sex) and a button to add more columns. when user clicks on add more, another 5 textboxes will be shown and then i'll be taking 5 + 5 (textbox values) and comma seperate them and need to insert into a table with primary auto increment key ID and the remaining 5 columns(firstname, lastname, email, age, sex).
I previously worked on the same requirmeent but with only single column.
Now i need to insert for 5 columns, How can i do this?
Below is my stored proc which i used for single column insertion.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_add_hotel_amenities_1]
@hotel_amenities_id_list varchar(4000),
@hotel_id varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pos int, @curruntLocation char(20)
SELECT @pos=0
SELECT @hotel_amenities_id_list = @hotel_amenities_id_list + ','
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list)
WHILE @pos > 0
BEGIN
SELECT @curruntLocation = RTRIM(SUBSTRING(@hotel_amenities_id_list,1,@pos-1))
INSERT INTO ref_hotel_amenities (hotel_amenity_id, hotel_id) VALUES (CONVERT(bigint,@curruntLocation), @hotel_id)
SET @hotel_amenities_id_list = RIGHT(@hotel_amenities_id_list, LEN(@hotel_amenities_id_list) - @Pos)
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list, 1)
END
-- LIST THE RESULTS
SELECT @hotel_amenities_id_list=SUBSTRING(@hotel_amenities_id_list,@pos+1,4000)
END
March 22, 2010 at 3:53 am
Did not really look at that SP as your requirement and what it does looks entirely different. I am guessing you need a function to split it and create a table out of it. (I could be wrong).
I'd suggest you to read this article and see if it fits in your case.
http://www.sqlservercentral.com/articles/T-SQL/63003/
---------------------------------------------------------------------------------
March 22, 2010 at 3:58 am
Hi,
you can solve this problem by using dynamic SQL.
In the first step analyse all existing values and add them to a result string including comas and quotation marks like 'Firtsname', 'Second Name', a.s.o.
In the second step add NULL values t the result string for each missing value.
In the last step build he dynamic SQL statement.
But note: the values mus be always in the same order.
Yust understand the code below as idea.
ALTER PROCEDURE [dbo].[usp_add_hotel_amenities_1]
@hotel_amenities_id_list varchar(4000),
@hotel_id varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pos int
DECLARE @s_insert VARCHAR(4000)
DECLARE @s_sql VARCHAR(MAX)
DECLARE @n_colum INT
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list)
SET @n_colum = 0
-- analyse existing values
WHILE @pos > 0
BEGIN
-- build dynamic SQL
SET @s_insert = @s_insert + '''' + RTRIM(SUBSTRING(@hotel_amenities_id_list,1,@pos-1)) + '',''
-- reduce list of values
SET @hotel_amenities_id_list = RIGHT(@hotel_amenities_id_list, LEN(@hotel_amenities_id_list) - @Pos)
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list, 1)
SET @n_colum = @n_colum + 1
END
-- add NULL for each missing value
WHILE @n_colum < 10
BEGIN
SET @s_insert = @s_insert + 'NULL, '
END
SET @s_sql = 'INSERT INTO xxxx (all 10 fields) ' +
'VALUES (' + @s_insert + ')'
EXEC sp_executeSql(@s_sql)
END
March 22, 2010 at 7:12 pm
You can pass it as XML parameter, parse and insert it.
Thanks,
Nikul
March 22, 2010 at 11:37 pm
FraToLa (3/22/2010)
Hi,you can solve this problem by using dynamic SQL.
In the first step analyse all existing values and add them to a result string including comas and quotation marks like
'Firtsname', 'Second Name',a.s.o.In the second step add NULL values t the result string for each missing value.
In the last step build he dynamic SQL statement.
But note: the values mus be always in the same order.
Yust understand the code below as idea.
ALTER PROCEDURE [dbo].[usp_add_hotel_amenities_1]
@hotel_amenities_id_list varchar(4000),
@hotel_id varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pos int
DECLARE @s_insert VARCHAR(4000)
DECLARE @s_sql VARCHAR(MAX)
DECLARE @n_colum INT
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list)
SET @n_colum = 0
-- analyse existing values
WHILE @pos > 0
BEGIN
-- build dynamic SQL
SET @s_insert = @s_insert + '''' + RTRIM(SUBSTRING(@hotel_amenities_id_list,1,@pos-1)) + '',''
-- reduce list of values
SET @hotel_amenities_id_list = RIGHT(@hotel_amenities_id_list, LEN(@hotel_amenities_id_list) - @Pos)
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list, 1)
SET @n_colum = @n_colum + 1
END
-- add NULL for each missing value
WHILE @n_colum < 10
BEGIN
SET @s_insert = @s_insert + 'NULL, '
END
SET @s_sql = 'INSERT INTO xxxx (all 10 fields) ' +
'VALUES (' + @s_insert + ')'
EXEC sp_executeSql(@s_sql)
END
Hi FraToLa,
Thanks for the comeback..
I've executed the query and understood how it works..
But i'm getting incorrect syntax error near
Msg 102, Level 15, State 1, Procedure usp_test_multiple_inserts, Line 23
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure usp_test_multiple_inserts, Line 42
Incorrect syntax near '@s_sql'.
Here is my modified Query:
Can you please check it once..
CREATE PROCEDURE [dbo].[usp_test_multiple_inserts]
@hotel_amenities_id_list varchar(4000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pos int
DECLARE @s_insert VARCHAR(4000)
DECLARE @s_sql VARCHAR(MAX)
DECLARE @n_colum INT
--Set @hotel_amenities_id_list='IV100099,E56565-PRTD-8889-00,Mr,Pavan,Puligandla,17-05-1985 11:30:00,;'
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list)
SET @n_colum = 0
-- analyse existing values
WHILE @pos > 0
BEGIN
-- build dynamic SQL
SET @s_insert = @s_insert + '''' + RTRIM(SUBSTRING(@hotel_amenities_id_list,1,@pos-1)) + '',''
-- reduce list of values
SET @hotel_amenities_id_list = RIGHT(@hotel_amenities_id_list, LEN(@hotel_amenities_id_list) - @Pos)
SET @Pos = CHARINDEX(',', @hotel_amenities_id_list, 1)
SET @n_colum = @n_colum + 1
END
-- add NULL for each missing value
WHILE @n_colum < 10
BEGIN
SET @s_insert = @s_insert + 'NULL, '
END
SET @s_sql = 'INSERT INTO customer_booking_members (booking_reference_id, customer_id, title, first_name, last_name, date_of_birth) ' +
'VALUES (' + @s_insert + ')'
EXEC sp_executeSql(@s_sql)
END
Many Thanks..
March 23, 2010 at 2:43 am
Hi,
maybe it is a problem with line
SET @s_insert = @s_insert + '''' + RTRIM(SUBSTRING(@hotel_amenities_id_list,1,@pos-1)) + '',''
If the statement is not built properly than the execute will fail. You can check this by adding
PRINT @s_insert behind the SET statement.
At least you need something like this 'firstname','lastname'.
If you add NULL values then ignore the quotation marks and eleminate the last coma.
March 23, 2010 at 3:17 am
Its better to have a table valued function returning table from the comma seperated list of values as a input parameter.
And then use select with this function to insert the records.
Code will be simpler.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply