Click here to Skip to main content
14,603,943 members
Rate this:
Please Sign up or sign in to vote.
problem
how to execute stored procedure with every row of temp table ?

I need to execute storedprocedurename inputfilepath outputfilepath. this is data exist on database on table Importertemplate. i need to get stored procedure name after execute with two parameters inputfilepath and outputfilepath. i need to loop with every row get storedprocedurename and inputfilepath and outpupath and execute it.

so how to loop within it it and execute stored procedure with every row'
I can write it but within loop cannot do it.

What I have tried:

create Proc ImporterQueue_RunModified1
As
BEGIN
WITH CTE AS
(
    SELECT Row_Number() OVER (ORDER BY GetDate()) AS rownumber, 
           StoredProcedureName, 
           ImporterQueue.CreateBy, 
           ImporterQueueID,
           applicationid, 
           dbo.ImporterTemplate.ImporterTemplateID, 
           InputFilePath, 
           OutputFilePath, 
           StoredProcedureName [ImporterTemplate.StoredProcedureName],
           RN = ROW_NUMBER() OVER (PARTITION BY applicationid ORDER BY ImporterQueueID ASC)
    FROM dbo.ImporterQueue WITH (NOLOCK) 
    INNER JOIN dbo.ImporterTemplate WITH (NOLOCK)  ON dbo.ImporterQueue.ImporterTemplateID = dbo.ImporterTemplate.ImporterTemplateID
    INNER JOIN Privilages.Module ON dbo.ImporterTemplate.ModuleID = Privilages.Module.ModuleID
    WHERE dbo.ImporterQueue.IsDeleted = 0 
    AND dbo.ImporterQueue.OverAllStatusID = 1 
)
SELECT rownumber , 
       RN , 
       ImporterQueueID,
       CreateBy,
       StoredProcedureName,
       InputFilePath,
       OutputFilePath
 INTO #results   FROM    CTE
    WHERE   RN = 1;

IF (
       SELECT OverAllStatusID 
       FROM dbo.ImporterQueue 
       INNER JOIN #results ON ImporterQueue.ImporterQueueID = #results.ImporterQueueID) <> 1 -- Pending
RETURN;
//here i need to write 
EXEC storedprocedurename inputfilepath outputpath
END
Posted
Updated 9-Nov-19 5:20am
v2
Comments
#realJSOP 9-Nov-19 9:16am
   
What SQl are we talking aabout? Sql Server? MySql? SomeOtherF*ckedUpSQL?
ahmed_sa 9-Nov-19 10:25am
   
sql server 2012

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

While it is not something I generally recommend; it may just be easiest to use a CURSOR for this application.
This is a rough sample of what it could look like, it is up to use appropriate variable types/sizes and to work out what the SELECT statement actually will be; so consider this a starting point
DECLARE @Input NVARCHAR(64)
DECLARE @Ouput NVARCHAR(64)

DECLARE rs CURSOR LOCAL STATIC FOR
	SELECT InputFilePath, OutputFilePath
	FROM   #results
	WHERE  -- to be determined by you
	FOR READ ONLY

	OPEN rs
		FETCH rs INTO @Input, @Output
		WHILE (@@Fetch_Status = 0) BEGIN
			EXECUTE ProcedureName @Input, @Ouput
			FETCH rs INTO @Input, @Output
		END
	CLOSE rs
DEALLOCATE rs
   
Comments
Afzaal Ahmad Zeeshan 9-Nov-19 15:22pm
   
CURSOR was also something that I thought about by reading the first few lines of the code.

5ed.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100