how to force a procedure to execute the first ?
-
I will force the cursor to execute the procedure first then execute execute the others
my script is as follows
create procedure [dbo].[usp_IntegrationAllStoredProcedures]
as
set nocount on;
DECLARE abc CURSOR FOR
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.routines
WHERE ROUTINE_TYPE = 'PROCEDURE' and right(ROUTINE_NAME,4)='Bulk' and ROUTINE_NAME !='usp_Integration_CONTRAT_EMISSION_Bulk'
OPEN abc
DECLARE @RoutineName varchar(128)
-- Build select string once
DECLARE @SQLString nvarchar(2048)
FETCH NEXT FROM abc
INTO @RoutineName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'exec'+ ' ' + @RoutineName
print (@SQLString)
--EXECUTE sp_ExecuteSQL @SQLString
FETCH NEXT FROM abc
INTO @RoutineName
END
CLOSE abc
DEALLOCATE abc
Print 'Manque Procedure usp_Integration_CONTRAT_EMISSION_Bulk'
set nocount off;
GOmy goal is to execute my procedure usp_Integration_CONTRAT_Bulk the first
who has an idea please
- This topic was modified 4 years, 4 months ago by samirca007.
-
DECLARE abc CURSOR FOR
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.routines
WHERE ROUTINE_TYPE = 'PROCEDURE'
and right(ROUTINE_NAME,4)='Bulk'
and ROUTINE_NAME !='usp_Integration_CONTRAT_EMISSION_Bulk'
order by case ROUTINE_NAME
when 'usp_Integration_CONTRAT_Bulk' THEN ' ' + ROUTINE_NAME
else ROUTINE_NAME
end -
If order of execution is important for 1 procedure, it's likely inevitable that order of execution will be come a necessity for others, as well. My recommendation is to build a numbered control table and use that as the source of your control cursor.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You said cursor, wash your mouth out and say three Hail Marys to repent :-/
Far away is close at hand in the images of elsewhere.
Anon. - David Burrows wrote:
You said cursor, wash your mouth out and say three Hail Marys to repent :-/
😀 😀 😀
Agreed. Unless the list of stored procedures was in flux and needed to accommodate both deletions and additions of stored procedures , I don't understand why someone wouldn't just create a top level procedure to call the various procedures. Even then, a cursor isn't necessary. One could simply concatenate the EXEC commands. But, a cursor doesn't actually cause a performance issue here because it's strictly a control cursor.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My post was in jest 🙂
But agree with your post, I have a proc that calls several procs in a specific order with some logic to decide whether to call some of them.
Far away is close at hand in the images of elsewhere.
Anon. -
Oh no... not to worry. I got "the jest" of it. 😀 That's why the smiley faces.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply