March 11, 2004 at 9:54 am
Hi folks,
I need to execute a series of ranked SPROC from a calling Sproc. Can anyone please help point me in the right direction.
I am running a SQL Server 2000. I have a table called Ranking defined as;
CREATE TABLE dbo.Ranking
(
Row_ID INT IDENTITY(1,1) NOT NULL,
Cust_ID INT NOT NULL,
Rank_ID TINYINT NOT NULL,
SPName VARCHAR(55) NOT NULL
)
Sample data in this table:
Row_ID Cust_ID Rank_ID SPName
1 1 1 SP1
2 1 2 SP2
3 1 3 SP3
4 1 4 SP4
5 1 5 SP5
6 2 1 SPxx
7 2 2 SPxy
8 3 1 SPbbbb
9 4 1 SP13
Each SPROC receives the same input/output parameters and RETURN an INT value.
I want to call all the SPROCs for a given Cust_ID in their ranking order from a calling stored proc (accepts a cust_id input parameter) simultaneously and be able to:
a) determine when they have all run
b) interogate their respective RETURN value and make decisions based on the latter.
Thanking you in advance for your help.
March 11, 2004 at 12:10 pm
Try using this procedure. I just wote it and didn't even check for syntax but you should get the ides.
CREATE PROCEDURE usp_ExecuteMyProc
(@ProcedureName varchar(256)
,@Parameter1 varchar(1000) -- Use as manu parameters as required
,@Parameter2 int -- Name them appropriately
,@Parameter3 smallint -- usint the appropriate variable type
,@Parameter4 varchar(1000)
)
AS
DECLARE @SQL varchar(2000)
DECLARE @NSQL nvarchar(1000)
DECLARE @Rtn int
BEGIN
-- Create a local temporary table
CREATE TABLE #RtnCode
(Rtn int)
-- Build Dynamic SQL based on the passed parameters
-- Placing the return value in the local temporary table
SET @SQL = ''
SET @SQL = @SQL + 'DECLARE @Rtn int'
SET @SQL = @SQL + 'EXECUTE @Rtn = ' + @ProcedureName
SET @SQL = @SQL + ' ''' + @Parameter1 + ''''
SET @SQL = @SQL + ', ' + CAST(@Parameter2 as varchar)
SET @SQL = @SQL + ', ' + CAST(@Parameter3 as varchar)
SET @SQL = @SQL + ', ''' + @Parameter4 + ''''
SET @SQL = @SQL + ' '
SET @SQL = @SQL + 'INSERT INTO #RtnCode(Rtn) VALUES (@Rtn)'
SET @NSQL = CAST(@SQL as nvarchar(1000))
-- Execute the dynamic SQL
EXECUTE sp_ExecuteSQL
-- Retrieve the return value
SET @Rtn = (SELECT Rtn FROM #RtnCode)
-- Cleanup
DROP TABLE #RtnCode
-- Return the return value
RETURN @Rtn
END
March 12, 2004 at 2:28 am
Thanks srankin for your reply. But this solution will run each SPROC for a given customer in turn and not run all SProc at the same time.
Ex: Cust_ID 1 has 5 SPROC (SP1,SP2,SP3,SP4,SP5) and we want to fire each of these "at the same time" without waiting for a result (return value).
However, the return value from each sproc will be used in a "decision tree" according to the ranking (as if they were run under different SPID).
March 12, 2004 at 2:46 am
A quick and dirty solution: fire them out of the SQL Server agent. There you can define the same execution start time so their will fire (more or less) at the same time.
An other solution is to write a multi threading (exeternal) application which will fire those SPs
Bye
Gabor
March 12, 2004 at 6:04 am
You could create a package that has the seperate stored procedures as a seperate SQL task. The default for concurrency is 4, but you can change it.
It is possible to start a package from a stored procedure and pass parameters from the starting stored procedure to the package and all the stored procedures within the package by using global parameters.
Have a look at http://www.databasejournal.com/features/mssql/article.php/1459181
to see how its done.
August 2, 2005 at 5:06 pm
I have similar problem:
I have 1 procedure which I/users will run from the QA, but I need to execute it simultaneously with different params - just to save time...
If I do as bellow - is it going to execute them in parallel or is going to wait for the first one to finish and then start the second?
create procedure execute_my_proc as
exec my_test (param1, param2)
exec my_test (param3, param4)
..
exec my_test (param13, param14).
go
Except that - every time the proc runs, it prints the output, which I need to save to a file or at least to review for errors?
How the output will look? Am I going to lose the previous ones?
Thanks a lot,mj
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply