问题描述:

I had posted this stored procedure earlier because of a different issue that was resolved. But I have noticed that when I execute the stored procedure, and give it the values that it will be receiving from other code, it is only inserting on the Quiz table and not the other 2 tables, Games and Video.

Here is the stored procedure

CREATE PROCEDURE s_Add$Subject$Info

(

@SubjectName varchar(50),

@SubjectDescription varchar(max),

@QuizIDString varchar(100),

@GamesIDString varchar(100),

@VideoIDString varchar(100)

)

as

--declare variables

declare @theSubID int

declare @quizIDLength int

declare @gamesIDLength int

declare @videoIDLength int

declare @quizIDs varchar(200)

declare @gamesIDs varchar(200)

declare @videoIDs varchar(200)

declare @numberCount int

declare @sTemp varchar(100)

--initializing variables

set @quizIDLength = LEN(@QuizIDString)

set @gamesIDLength = LEN(@GamesIDString)

set @videoIDLength = LEN(@VideoIDString)

set @quizIDs = ''

set @gamesIDs = ''

set @videoIDs = ''

set @numberCount = 1

--declare temp tables

declare @Q table --Quiz

(

QuizID varchar(100),

SubjectID int

)

declare @G table --Games

(

GamesID varchar(100),

SubjectID int

)

declare @V table --Videos

(

VideoID varchar(100),

SubjectID int

)

insert into[Subjects]

(SubjectName, SubjectDescription)

values

(@SubjectName, @SubjectDescription)

set @TheSubID = @@IDENTITY

--Check to see what strings are full then entering ids if true

--Quizzes first

if(@QuizIDLength > 0)

begin

while(@numberCount <[email protected])

begin

set @sTemp = SUBSTRING(@QuizIDString, @numberCount, 1)

if(@sTemp = ',')

begin

insert into @Q (QuizID, SubjectID) values (@quizIDs, @theSubID)

set @quizIDs = ''

end

if(@sTemp <> ',')

begin

set @quizIDs = @quizIDs + @sTemp

end

set @numberCount = @numberCount + 1

end

end

--Games second

if(@gamesIDLength > 0)

begin

while(@numberCount <[email protected])

begin

set @sTemp = SUBSTRING(@GamesIDString, @numberCount, 1)

if(@sTemp = ',')

begin

insert into @G (GamesID, SubjectID) values (@gamesIDs, @theSubID)

set @gamesIDs = ''

end

if(@sTemp <> ',')

begin

set @gamesIDs = @gamesIDs + @sTemp

end

set @numberCount = @numberCount + 1

end

end

--Videos third

if(@videoIDLength > 0)

begin

while(@numberCount <[email protected])

begin

set @sTemp = SUBSTRING(@VideoIDString, @numberCount, 1)

if(@sTemp = ',')

begin

insert into @V (VideoID, SubjectID) values (@videoIDs, @theSubID)

set @videoIDs = ''

end

if(@sTemp <> ',')

begin

set @videoIDs = @videoIDs + @sTemp

end

set @numberCount = @numberCount + 1

end

end

--inserting the ids to subjectinfo table

insert into [SubjectInfo]

(SubjectID, QuizID)

select SubjectID, QuizID

from @Q

--Games

insert into [SubjectInfo]

(SubjectID, GameID)

select SubjectID, GamesID

from @G

--Video

insert into [SubjectInfo]

(SubjectID, VideoID)

select SubjectID, VideoID

from @V

I have tried putting the insert in on its respective if statement, and I even tried using an inner join to do the insert at one shot, but it didn't really work out to well.

So why would the data only insert on the first insert, using my declared tables, and not on the others?

I could fix this by coding it with C#/ASP.NET, but then I would have to open a connection multiple times and I don't really want to do that because I'm already writing the stored procedure to insert what I need so I may as well deal with it in the stored procedure while I'm writing it.

Any ideas or can anyone see what I am doing wrong?

相关阅读:
Top