CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlcursor
wootscootinboogie
wootscootinboogie

Reputation: 8695

Running stored procedure/UDF X number of times when proc inserts rows

I've got a stored procedure that looks like:

create proc spInsertDrugQuestions  
@drugId int  
as  
begin  
declare @drugName varchar(50)= (select distinct drugName  
        from Drugs  
        where DrugId = @drugId)  
--class question and answer  
declare @drugClassQuestion varchar(250) =   
        'What is the drug class of your ' + @drugName          

declare @drugClassAnswer varchar(50) = (select distinct drugClass  
             from drugs  
             where drugId = @drugId)  
--dosage question and answer  
declare @drugDosageQuestion varchar(250) =   
      'What is the dosage of your '  + @drugName  

declare @drugDosageAnswer varchar(50) = (select distinct drugDosage  
           from drugs  
           where drugId = @drugId)  

--QuizQuestionTypeId is a foreign key to another table not shown
--but indicates the general type of question (dosage, class, etc.)
insert into DrugQuestions(DrugId,DrugQuestion,CorrectAnswer,QuizQuestionTypeId)  
values  (@drugId,@drugClassQuestion,@drugClassAnswer,3)  
  ,(@drugId,@drugDosageQuestion,@drugDosageAnswer,1)  
end

This stored procedure is for an end user who enters in new information into the Drugs table and the corresponding questions in the DrugQuestions table will be added. For the time being, however, I need to be able to run this stored procedure for every DrugId that currently exists in the Drugs table. I think the best way to do this is use a UDF and CROSS APPLY joining on a numbers table that will have every DrugId. However, I'm not too familiar with UDFs and the following attempt 

create function fnInsertDrugQuestions(@drugId int)
returns int --think can't be right, but it didn't give me any read squigglies
as begin
declare @drugName varchar(50)= (select distinct drugName  
        from Drugs  
        where DrugId = @drugId)  
--class question and answer  
declare @drugClassQuestion varchar(250) =   
        'What is the drug class of your ' + @drugName          

declare @drugClassAnswer varchar(50) = (select distinct drugClass  
             from drugs  
             where drugId = @drugId)  
--dosage question and answer  
declare @drugDosageQuestion varchar(250) =   
      'What is the dosage of your '  + @drugName  

declare @drugDosageAnswer varchar(50) = (select distinct drugDosage  
           from drugs  
           where drugId = @drugId)  
insert into DrugQuestions(DrugId,DrugQuestion,CorrectAnswer,QuizQuestionTypeId)  
values  (@drugId,@drugClassQuestion,@drugClassAnswer,3)  
  ,(@drugId,@drugDosageQuestion,@drugDosageAnswer,1)  

end

gives me the errors: 

Msg 443, Level 16, State 15, Procedure fnInsertDrugQuestions, Line 21
Invalid use of a side-effecting operator 'INSERT' within a function.
Msg 455, Level 16, State 2, Procedure fnInsertDrugQuestions, Line 21
The last statement included within a function must be a return statement.

I need to know a couple things:

1.) Is it possible to insert data like this using a UDF?
2.) Is it possible to used `CROSS APPLY` with a stored procedure?
3.) Do I really want to loop through all the `DrugId`s to do this?

Upvotes: 0

Views: 163

Answers (1)

GarethD
GarethD

Reputation: 69769

  1. No - Use a stored procedure if you want to insert data, you can still return data from a stored procedure if required.
  2. Yes - You can use CROSS APPLY in a stored procedure.
  3. No - You could just use the following to insert all current drugs, especially if it is being done as a one off:
INSERT DrugQuestions (DrugId, DrugQuestion, CorrectAnswer, QuizQuestionTypeId)
SELECT  DrugID,
        DrugQuestion = t.Question + drugs.DrugName,
        CorrectAnswer = t.Answer,
        t.QuizQuestionTypeId
FROM    drugs
        CROSS APPLY
        (   VALUES
                ('What is the drug class of your ', drugs.DrugClass, 3),
                ('What is the dosages of your ', drugs.drugDosage, 1)
        ) t (Question, Answer, QuizQuestionTypeId);

If you are bulk inserting drugs i.e. multiple drugs at a time from a user, rather than repeating the same function for each new drug, you can use the output clause to collect new drugIDs then combine with the above to add new questions:

DECLARE @NewDrugIDs TABLE (ID INT NOT NULL);
INSERT Drugs (DrugName, DrugDosage, DrugClass, ...)
OUTPUT inserted.drugID INTO @NewDrugIDs
SELECT/VALUES   ...

INSERT DrugQuestions (DrugId, DrugQuestion, CorrectAnswer, QuizQuestionTypeId)
SELECT  DrugID,
        DrugQuestion = t.Question + drugs.DrugName,
        CorrectAnswer = t.Answer,
        t.QuizQuestionTypeId
FROM    drugs
        CROSS APPLY
        (   VALUES
                ('What is the drug class of your ', drugs.DrugClass, 3),
                ('What is the dosages of your ', drugs.drugDosage, 1)
        ) t (Question, Answer, QuizQuestionTypeId)
WHERE   drugs.DrugID IN (SELECT ID FROM @NewDrugIDs);

Upvotes: 1

Related Questions