I am writing a Stored Procedure to delete all records from child tables (some of which do not have any FK indexes, working with someone's database) and sub-child tables.
Here are the relevant codes:
@plan_id int=4,
@pr_id int,
@pbr_id int,
@pbfr_id int
)
BEGIN
DECLARE planrate_cursor CURSOR FOR
SELECT planrate_id AS pr_id
FROM dbo.qe_planrate
WHERE plan_id = @plan_id
OPEN planrate_cursor
FETCH NEXT FROM planrate_cursor INTO @pr_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE planbaserate_cursor CURSOR FOR
SELECT planbaserate_id AS pbr_id
FROM dbo.qe_planbaserate
WHERE (planrate_id = @pr_id)
OPEN planbaserate_cursor
FETCH NEXT FROM planbaserate_cursor INTO @pbr_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE dbo.qe_planbaserate
WHERE (planbaserate_id = @pbr_id)
FETCH NEXT FROM planbaserate_cursor INTO @pbr_id
END
CLOSE planbaserate_cursor
DEALLOCATE planbaserate_cursor
DECLARE planbasefixrate_cursor CURSOR FOR
SELECT planbasefixrate_id AS pbfr_id
FROM dbo.qe_planbasefixrate
WHERE (planrate_id = @pr_id)
OPEN planbasefixrate_cursor
FETCH NEXT FROM planbasefixrate_cursor INTO @pbfr_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE dbo.qe_planbasefixrate
WHERE (planbasefixrate_id = @pbfr_id)
FETCH NEXT FROM planbasefixrate_cursor INTO @pbfr_id
END
CLOSE planbasefixrate_cursor
DEALLOCATE planbasefixrate_cursor
END
DELETE dbo.qe_planrate
WHERE planrate_id = @pr_id
CLOSE planrate_cursor
DEALLOCATE planrate_cursor
END
The error I receive is:
Procedure 'usp_my_sproc' expects parameter '@pr_id', which was not supplied.
If anyone has any thoughts about why I am getting this error, I'd appreciate. Yes, I have searched Google and MSDN and MS Technet and can not figure out how to fix this error.
========================================
Buh Bye!
Cheers,
Me
Here are the relevant codes:
Code:
( @plan_id int=4,
@pr_id int,
@pbr_id int,
@pbfr_id int
)
BEGIN
DECLARE planrate_cursor CURSOR FOR
SELECT planrate_id AS pr_id
FROM dbo.qe_planrate
WHERE plan_id = @plan_id
OPEN planrate_cursor
FETCH NEXT FROM planrate_cursor INTO @pr_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE planbaserate_cursor CURSOR FOR
SELECT planbaserate_id AS pbr_id
FROM dbo.qe_planbaserate
WHERE (planrate_id = @pr_id)
OPEN planbaserate_cursor
FETCH NEXT FROM planbaserate_cursor INTO @pbr_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE dbo.qe_planbaserate
WHERE (planbaserate_id = @pbr_id)
FETCH NEXT FROM planbaserate_cursor INTO @pbr_id
END
CLOSE planbaserate_cursor
DEALLOCATE planbaserate_cursor
DECLARE planbasefixrate_cursor CURSOR FOR
SELECT planbasefixrate_id AS pbfr_id
FROM dbo.qe_planbasefixrate
WHERE (planrate_id = @pr_id)
OPEN planbasefixrate_cursor
FETCH NEXT FROM planbasefixrate_cursor INTO @pbfr_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE dbo.qe_planbasefixrate
WHERE (planbasefixrate_id = @pbfr_id)
FETCH NEXT FROM planbasefixrate_cursor INTO @pbfr_id
END
CLOSE planbasefixrate_cursor
DEALLOCATE planbasefixrate_cursor
END
DELETE dbo.qe_planrate
WHERE planrate_id = @pr_id
CLOSE planrate_cursor
DEALLOCATE planrate_cursor
END
The error I receive is:
Procedure 'usp_my_sproc' expects parameter '@pr_id', which was not supplied.
If anyone has any thoughts about why I am getting this error, I'd appreciate. Yes, I have searched Google and MSDN and MS Technet and can not figure out how to fix this error.
========================================
Buh Bye!
Cheers,
Me