none
How to execute stored procedure with every row of temp table by loop with every row ? RRS feed

  • Question

  • problem
    how to execute stored procedure with every row of temp table by loop with every row ?

    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 sp input output based on loop for every row
    Exec storedprocedurename inputfilepath outputpath
    END


    Saturday, November 9, 2019 12:33 PM

Answers

  • 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;
    


    Hi engahmedbarbary, 

    I noticed that you have a query in your procedure . Maybe you would like to use a table value as  parameters in a stored procedure . 

    Please check following link . Table-Valued Parameters

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 6:24 AM

All replies

  • Use a cursor. Or worse - a while loop that simulate a cursor (which you already know how to do in your previous question). Or change the stored procedure to use a TVP. And stop splattering table hints all over your code without an obvious and good reason. Are you prepared to handle the errors and the incorrect information that using NOLOCK can cause? 

    If I had to guess, your use of row_number implies that there is something terribly wrong about your design as it appears you do this to get some unique set of values. This, in turn, suggests your CTE query is logically flawed and you are using row_number to avoid fixing it. But if your procedure has only 2 parameters, I must question why your cte query selects so many UNUSED columns. 

    But now I see another potential complication. "storedprocedurename" is a column in your CTE. So apparently this will also require dynamic sql. You should get help from your coworkers, your seniors, your dba, your manager, someone local that has the expertise to guide you. 

    Saturday, November 9, 2019 2:05 PM
  • can you help me apply using cursor i dont work on cursor before 

    and another question why not use while 

    Saturday, November 9, 2019 3:29 PM
  • can you write SQL query with your suggestion 

    i removed columns not i needed 

    as following

    create Proc ImporterQueue_RunModified1
    As
    BEGIN
    WITH CTE AS
        (
    	 StoredProcedureName , ImporterQueue.CreateBy , ImporterQueueID,applicationid, InputFilePath, OutputFilePath, ,
                    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  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 sp input output based on loop for every row
    Exec storedprocedurename inputfilepath outputpath
    END
    cte used to get first record from every group based on app;icationid

    Saturday, November 9, 2019 3:56 PM
  • can you help me to write loop with your suggestion code if possible
    Saturday, November 9, 2019 3:57 PM
  • 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;
    


    Hi engahmedbarbary, 

    I noticed that you have a query in your procedure . Maybe you would like to use a table value as  parameters in a stored procedure . 

    Please check following link . Table-Valued Parameters

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 6:24 AM