December 14, 2005 at 8:08 am
I have the following stored proc. I was wondering how I can accomplish this.
CREATE PROCEDURE proc_GetIssuedProjectsForFinalTierBilling
AS
SELECT task.project_id, mesa_task_no, project + ' ' + location,
exec proc_GetEvalutaionIdByProjectId @project_id = task.project_id
--The stored proc above checks if there is a record on another table for this project (task) and if there is not a record it adds one. At the end it returns the identity for the record that corresponds with this task.
FROM task
WHERE (status_id = 6) AND ((division_id = 1) OR (division_id = 2)) AND (client_id = 1)
GO
--I tried using a UDF but UDF does not allow me to do an insert.
Any ideas?
Thanks,
December 14, 2005 at 12:03 pm
I think you need to make the procedure you are calling a function so that you can use it in a select list
December 14, 2005 at 12:23 pm
I tried creating a UDF but it does not allow me to use an INSERT statement.
CREATE FUNCTION udf_GetEvaluationIdByProjectId(@project_id int)
RETURNS int
AS
BEGIN
Declare @count int
Declare @id int
Select @count = Count(id) FROM tva_evaluation WHERE (project_id = @project_id)
/*
IF @count = 0
BEGIN
INSERT INTO tva_evaluation
(project_id,tier_1,tier_2)
VALUES
(@project_id,0,0)
END
*/
SELECT @id = id
FROM tva_evaluation
WHERE (project_id = @project_id)
Return @id
END
I get an error saying that I cannot use INSERT on a function. any ideas?
December 14, 2005 at 12:42 pm
I would change your proc to do a few steps -
1. Identifiy the records you would need to insert and possibly put the records to insert into a temp table or table variable
2. Insert the records
3.perform your select statement
All of these could be accomplished in your original stored procedure without the use of a second procedure or funtion.
December 15, 2005 at 10:12 am
I figured out.
first I did an insert on the ones that met the (not exists) parameter
then did my select query
Nevertheless, thanks for your input.
XC
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply