November 30, 2017 at 4:31 am
Hi
I have a stored procedure that is executed by many processes in the same time (all processes are starting in the same time and they are running my stored procedure).
What is the solution from SQL Server point of view to run sql server stored procedure JUST ONE TIME ?
I mention that i don't have control at application level and i cant't use mechanisms like WAIT FOR DELAY.
(Sql Version: 2005 Standard Ed SP1 )
Thx.
G.
November 30, 2017 at 4:38 am
How do you decide which process should run the stored procedure? What are these processes - are they from the application? Why do you need to do this, by the way - we may be able to suggest a better solution?
John
November 30, 2017 at 4:49 am
I have an application who has some stored procedures behind, and i don't have source code for that application . I changed a stored procedure that execute an another stored procedure that is call only when is certain conditions are met. For me is enought to execute my procedure just one time (first time).
G.
November 30, 2017 at 4:54 am
Which stored procedure do you want to restrict to a single execution - the calling one, or the called one? And what do you mean by "first Time", given that you said in your first post that they all start at the same time? I think you're going to need to post some code, please.
Have you checked whether changing the stored procedure may invalidate any support agreement you have with the vendor of the application? I suspect it will.
John
November 30, 2017 at 5:06 am
USE ApplicationDatabase;
GO
ALTER PROCEDURE original_application_stored_procedure <-- this procedure is executed many times
@param1 nvarchar(50),
@param2 nvarchar(50)
AS
SET NOCOUNT ON;
------------------------New procedure call start---------
if (logical conditions)
begin
execute stored_procedure_to_be_exec_first_time_only <-- this procedure is executed many times olso and i want to be execute only one time (at first stored procedure call)
end
------------------------New procedure call end---------
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
GO
ps: no more technical support 🙁
G.
November 30, 2017 at 7:10 am
That isn't even going to compile, since you haven't declared @FirstName and @LastName. Also, please post the definition of stored_procedure_to_be_exec_first_time_only.
Do you only want stored_procedure_to_be_exec_first_time_only to be executed once - ever? If not, what are the criteria for deciding when it can be executed again?
John
November 30, 2017 at 7:17 am
Seems like you need to add some logic so that this
if (logical conditions)
begin
execute stored_procedure_to_be_exec_first_time_only <-- this procedure is executed many times olso and i want to be execute only one time (at first stored procedure call)
end
changes to this
if (logical conditions) and (proc has not been run already – whatever that means)
begin
execute stored_procedure_to_be_exec_first_time_only <-- this procedure is executed many times olso and i want to be execute only one time (at first stored procedure call)
end
How you add that additional test depends on what the called proc is doing. Is there a simple test you can add? If not, it may be necessary to add some logging to this solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply