SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 storedprocedure help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

salwamirza
Starting Member

2 Posts

Posted - 01/24/2014 :  04:13:44  Show Profile  Reply with Quote
Hi,
I've a webform that has five fields,, the first four fields has textboxes and the fifth one is a checkbox list. All the data of textboxes has to be saved in a table tbl_Workshop.
the checkbox list has list of trainers, a user can select for that particular wokrshop.. the checkbox selected items should go in tbl_WorkshopTrainers that has columns workshopId and trainerID.
I wrote a below storedprocedure to insert the webform data into my db.
but this is taking just one trainerID, how abt inserting as many trainers as the user selects from the chkboxlist?

alter procedure sp_InsertWorkshopTrainers
(
@Title as varchar(50),
@Topic as varchar(50),
@Date as date,
@Duration as varchar(50),
@CreatedDate as date,
@UpdatedDate as date,
@TrainerID as int
)
as
begin try
begin transaction tr_Insert
insert into dbo.tbl_Workshop
values(@Title,@Topic,@Date,@Duration,@CreatedDate,@UpdatedDate)

declare @WorkshopID as int
SET @WorkshopID=SCOPE_IDENTITY()
insert into dbo.tbl_WorkshopTrainer
values(@TrainerID,@WorkshopID)
commit transaction
end try
begin catch
rollback transaction tr_insert
end catch

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/24/2014 :  05:37:48  Show Profile  Reply with Quote
Make proc id as below
and pass trainer values as a comms separated list

the udf ParseValues used in logic can be found in below link
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

Create it first and then alter procedure as below

alter procedure sp_InsertWorkshopTrainers
(
@Title as varchar(50),
@Topic as varchar(50),
@Date as date,
@Duration as varchar(50),
@CreatedDate as date,
@UpdatedDate as date,
@TrainerIDList as varchar(1000)
)
as
begin try
begin transaction tr_Insert
insert into dbo.tbl_Workshop
values(@Title,@Topic,@Date,@Duration,@CreatedDate,@UpdatedDate)

declare @WorkshopID as int
SET @WorkshopID=SCOPE_IDENTITY()
insert into dbo.tbl_WorkshopTrainer
select Val,@WorkshopID
FROM dbo.ParseValues(@TrainerIDList,',')
commit transaction
end try
begin catch
rollback transaction tr_insert
end catch


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

salwamirza
Starting Member

2 Posts

Posted - 01/24/2014 :  06:32:43  Show Profile  Reply with Quote
How to create that?
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
573 Posts

Posted - 01/24/2014 :  07:51:44  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
create the function provided in the link and use it in the insert statement

Javeed Ahmed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000