Transact SQL syntax.

[jararaca]

Hi,

I am pretty new to stored procedures and still learning about the processing capabilities of Transact-SQL. Can anyone show me how to do the following:

I have a stored procedure that will receive several parameters. One of those is @QuestionID. QuestionID is a foreign key in Table X, whose primary key is XPK. Every record in Table X that has the value in QuestionID that matches the value in the @QuestionID parameter needs to have a new child record inserted into Table Y. Table Y has a foreign key that points to XPK.

Given that the values of the fields to be inserted into Table Y are supplied in parameters, how do I write the logic that will do this in a stored procedure? What is the proper syntax?

Thank you for your help.

Thank you.

 

[Jasen]

The gist of the operation will probably read something like:

insert into table_y (x, y, z, XPK) select x, y, z, XPK from table_x where QuestionID = @questionID

________
Remember, you're unique... just like everyone else.

 

[Jasen]

hmm, rethinking the question, this could be more complicated than I originally thought. I like cursors, so I'll show that method.

create procedure blah
   @questionID integer,
   @x varchar(10),
   @y varchar(10),
   @z varcahr(10),
   @XPK integer

declare c1 cursor for select XPK from table_x where QuestionID = @questionID

open c1

fetch next from c1 into @XPK

WHILE @@FETCH_STATUS = 0
   begin
       insert into table_y (x, y, z, QuestionID, XPK)
       values (@x, @y, @z, @questionID, @XPK)
       fetch next from c1 into @XPK
   end
close c1
deallocate c1

that sounds a bit more like what you asked.

________
Remember, you're unique... just like everyone else.

 

[mrdenny]

Cursors are much slower than recordset based processing. SQL Server is designed to use Recordset based processing. Why on earth would you want to recommend processing this row by row?

jararaca,
Please post some examples of how you are going to be passing your data into your variables. Also please post your table schemas. That will give a better idea of what you are trying to do.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Jasen]

Well to be fair, I didn't intend to imply that my code was the best solution, merely showing a quick example that performed the function I thought he was asking. I fully appreciate there's 15 different ways to accomplish anything, and I'm not experienced enough yet to know the best one. But I do enjoy learning.

________
Remember, you're unique... just like everyone else.

 

[jararaca]

Thank you, all, for this discussion. Jasen I appreciate your contribution, I learned something from it, and I look forward to learning more from you. I also appreciate your position that you are still learning (aren't we all? . And mrdenny seems to be making a point that can't be ignored. I am very much looking forward to the input from all.

In answer to mrdenny's request, here is more info:

Localized database schema:

Question 1--M QuestionItem (items a, b, c, etc. of multiple-choice questions, which they all are.)

Question 1--M QuestionnaireQuestion M--1 Questionnaire

QuestionnaireQuestion 1--M QuestionnaireQuestionItem

QuestionnaireQuestionItem holds override values to certain fields in QuestionItem. Users can set defaults in the QuestionItem table, and override the defaults when a question is attached to a questionnaire. The problem arises when editing questions that are already attached to questionnaires. I've got the edit and delete parts figured out and working fine, but my challenge now is to deal with this: What happens when a user adds an item to a question that is already attached to a questionnaire? In that case, that item has to be added to the QuestionnaireQuestionItem table so that the user can change the defaults, if desired. When the user adds a questionitem, I pass the QuestionID for that item, and the values of the fields being added. I then need a way to lookup all questionnaires in the QuestionnaireQuestion table that has that QuestionID, and also add that item to the QuestionnaireQuestionItem table for all such QuestionnaireQuestion records.

There are questions that come up, like, is this the best way of doing it? Why don't I just add the QuestionnaireQuestionItem row if the user changes the defaults? I am aware of these, but at this point I can't change things, due to the realities of deadlines and so on. I have to make this work, now, and deal with those other issues later.

I hope this helps. I think the original description I gave may have been less confusing, but there it is, anyway...

Thanks again, for your help.