May 18, 2006 at 1:23 pm
Hello,
I'm not at all familiar with coding looping structures within SQL Server 2000. I have a proc I need to use to INSERT an undetermined number of records. The proc is designed to handle one record, so I imagine it has to be called eight times in some way in order to insert each record.
The following is the proc I need to use;
**********************************************
CREATE procedure dbo.spv_PSI_AddMemberPSIGoal
@account bigint,
@company smallint,
@psi_count int,
@goal_desc nvarchar(500),
@goal_progress_id int,
@goal_progress_desc nvarchar(20) output,
@goal_status_id int,
@goal_status_desc nvarchar(20) output,
@is_selfcare_goal nvarchar(1),
@nt_id nvarchar(20),
@rowguid uniqueidentifier output,
@goal_id bigint output
as
declare @errornum int
begin
--create the rowguid
select @rowguid = newid()
--get the goal progress and status descriptions
select @goal_progress_desc = goal_progress_desc
from ah_master_goal_progress
where goal_progress_id = @goal_progress_id
select @goal_status_desc = goal_status_desc
from ah_master_goal_status
where goal_status_id = @goal_status_id
insert into ah_member_psi_goals (
company,
account,
psi_count,
goal_desc,
goal_progress_id,
goal_status_id,
is_selfcare_goal,
rowguid,
create_date,
create_user_code)
values
(@company,
@psi_count,
@goal_desc,
@goal_progress_id,
@goal_status_id,
@is_selfcare_goal,
@rowguid,
getdate(),
@nt_id
)
--Get the identity value for the previous insert to pass as an output parameter
select @goal_id = scope_identity()
if @@error > 0
begin
select @errornum = @@error
raiserror(@errornum, 16, 1)
end
if @is_selfcare_goal = 'Y'
begin
--self care goal is being added - mail it
exec spv_PSI_MailSelfCareGoal @account, @company, @nt_id
end
--add to history
exec dbo.spv_PSI_AddMemberPSIGoalHistory @rowguid, @nt_id
if @@error > 0
begin
select @errornum = @@error
raiserror(@errornum, 16, 1)
end
end
**************************************
Can this proc be worked into a looping structure in some way? If so, what would it look like?
Thank you for your ideas!
CSDunn
May 18, 2006 at 1:29 pm
"I'm not at all familiar with coding looping structures within SQL Server 2000."
Consider yourself lucky!! Most people are TOO familiar with looping structures. Anyway, you say that you need to INSERT an undetermined amount of rows. Where is the data comming from that will eventually determine how many rows need to be INSERTED?
May 18, 2006 at 1:37 pm
Soething that you can easily remove are:
if @@error > 0
begin
select @errornum = @@error ...
@errornum will always be 0 if done that way!!!!
you need to change that to
select @errornum = @@error
if @errornum > 0
begin ...
then follow John's advice above!!!
* Noel
May 18, 2006 at 2:15 pm
I'm thinking I'll probably use SELECT INTO to pull the data from where it needs to come from, then dump it into a temp table.
May 18, 2006 at 2:56 pm
As things stand, your stored procedure is already setup to accept in all of the column values that you need for your INSERT statement. You've got 2 choices here:
1. Whereever your data is comming from, create your loop there and call your stored procedure as-is (with the exception of adding Noel's error trapping suggestions).
2. Change your stored procedure to get the data, then INSERT it into your table.
That once again raised the question, where is the data comming from?
May 18, 2006 at 4:03 pm
The proc will be running against data that is in the same SQL Server 2000 database. I don't know if that answers your question about the data location or not. The query that will get the data looks like this;
SELECT
account,
psi_count,
goal_desc,
start_date,
end_date,
goal_status_id
FROM
ah_member_psi_goals_history
WITH(nolock)
WHERE
account = 20930153
and
psi_count = 4
May 18, 2006 at 4:11 pm
Yes, that is what I am asking. So, if I'm hearing you correctly, you run this query go SELECT your data into variables and then you call the INSERT stored procedure? If so, why not change your INSERT in your stored procedure to INSERT INTO....SELECT FROM like this:
INSERT INTO ah_member_psi_goals (column list)
SELECT <column list>
FROM ah_member_psi_goals_history
WHERE <filter data>
May 19, 2006 at 4:54 am
Well, I don't have rights to change the proc. I think what I am left with is some sort of loop that will go through and select each record, and fire the proc for each record. What would that kind of a loop look like?
It might just be easier to 'mannually' do the inserts for both tables using INSERT INTO...SELELCT FROM and not even use this proc.
May 19, 2006 at 7:38 am
Do you have the rights to create a new proc? Can you talk to the DBA who does have the rights, or is this a commercial app? Because, yes, even manually recreating the INSERT statement every time (which, obviously, you don't actually need to do, as at the very least you can save the SQL) will be less of a headache than using that sproc.
If you wanted to do it the other way, you'd pretty much have to use a cursor, AFAIK.
May 19, 2006 at 11:50 am
Thanks to all for the assistance!
CSDunn
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply