storedprocedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

storedprocedure help

Author

Topic

salwamirza
Starting Member

2 Posts

Posted - 2014-01-24 : 04:13:44

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

52326 Posts

Posted - 2014-01-24 : 05:37:48

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

salwamirza
Starting Member

2 Posts

Posted - 2014-01-24 : 06:32:43

How to create that?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-01-24 : 07:51:44

create the function provided in the link and use it in the insert statement

Javeed Ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources