How to call a stored proc once per each row of a table, without using CURSOR

  • Hello Everyone,

    I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

    Here are my simulated procs...

    Main Stored Procedure: This will be called once per each row of some table.

    -- All this proc does is, prints out the list of parameters that are passed to it.

    CREATE PROCEDURE dbo.MyMainStoredProc (

    @IDINT,

    @NameVARCHAR (200),

    @SessionIDINT

    )

    AS

    BEGIN

    -- Alright!...let me show the world what my parameter values are :)

    SELECT'SessionID - ' + CAST (@SessionID AS VARCHAR (20)) + ' called MyMainStoredProc with ID: ' + CAST (@ID AS VARCHAR (20)) + '; and Name: ' + @Name

    END

    GO

    Outer Stored Procedure: This will initiate calls to the proc above, "MyMainStoredProc"

    -- All this proc needs to do is, call MyMainStoredProc once per row of the table variable within this proc

    CREATE PROCEDURE dbo.MyOuterStoredProc (

    @SessionID INT

    )

    AS

    BEGIN

    SET NOCOUNT ON

    -- Create a dummy table (variable) with 3 dummy rows in it

    DECLARE @TestTable TABLE (ID INT, Name VARCHAR (200))

    INSERT @TestTable VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')

    -- Form a string that forms a call to MyMainStoredProc for each row of the dummy table

    DECLARE @myTestSQLString NVARCHAR (MAX) = N'', @myCounter INT = 0

    SELECT@myTestSQLString = @myTestSQLString + '

    EXEC dbo.MyMainStoredProc @ID = ' + CAST (ID AS VARCHAR) + ', @Name = ''' + Name + ''', ' + '@SessionID = ' + CAST (@SessionID AS VARCHAR) + ';

    ',

    @myCounter = @myCounter + 1

    FROM@TestTable

    -- PRINT @myTestSQLString

    -- Execute the string which have multiple sp_executesql calls, once per each row of the dummy table

    EXEC sp_executesql @myTestSQLString

    SET NOCOUNT OFF

    END

    Here is a sample call to the out proc...

    EXEC dbo.MyOuterStoredProc @SessionID = 123

    In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

    I would appreciate your suggestions to make this a more efficient solution without trivial limitations (like the string length I have in mine).

    - SC

  • There's no need to avoid cursors for such a thing. They're not the issue in such instances. The issue would be, what does the stored procedure do and why it has been written to support only RBAR.

    My recommendation would be to spend some time on a set-based rewrite of the stored proc.

    --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)

  • I agree: if you HAVE to do this one row at a time then a properly defined cursor is the correct way to do it. FAST_FORWARD or FORWARD ONLY, READ ONLY, STATIC, LOCAL.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • It may also be possible to re-write your SP as a Function and use CROSS APPLY. Just thinking out loud.


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • autoexcrement (7/10/2014)


    It may also be possible to re-write your SP as a Function and use CROSS APPLY. Just thinking out loud.

    That's actually a good idea if it can be written as an iTVF. Otherwise, it'll be about the same as the cursor solution.

    --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)

  • I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • Let us know a bit more about your business problem....I bet you don't need to call a stored proc in a cursory fashion like this, whether using a cursor or not! There is bound to be another way of doing what you need to do!

  • autoexcrement (7/10/2014)


    I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!

    Inline Table-valued functions are. Specifically inline, not any table valued function

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • autoexcrement (7/10/2014)


    I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!

    Apologies. I used an abbreviation that I thought everyone knew.

    As Gail stated, "INLINE Table Valued Functions" (or "iTVF" for short) will be faster than Scalar Functions. There are 3 basic types of UDFs. "Scalar" (SF), "Multi-statement Table Valued" (mTVF), and "Inline Table Valued" (iTVF). SFs can be dreadfully slow. mTVFs can be much worse. iTVFs are usually much faster than either of the other two. SFs and mTVFs require a BEGIN/END around the body of the function and iTVFs do not. iTVFs are written more like a parameterized view than anything else. This is also why a lot of people say that "If your function contains a "BEGIN", there's a good chance that it's slow".

    There are, of course, exceptions to every rule.

    --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)

  • From what I can gather, there is no need for a cursor. It can change depending on the actual work required!

    😎

    USE tempdb;

    GO

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_NAME = 'MyMainiTVF'

    AND ROUTINE_SCHEMA = 'dbo')

    DROP FUNCTION dbo.MyMainiTVF;

    GO

    -- All this proc does is, prints out the list of parameters that are passed to it.

    CREATE FUNCTION dbo.MyMainiTVF (

    @IDINT,

    @NameVARCHAR (200),

    @SessionIDINT

    )

    RETURNS TABLE

    AS

    RETURN

    -- Alright!...let me show the world what my parameter values are :)

    SELECT'SessionID - '

    + CAST (@SessionID AS VARCHAR (20))

    + ' called MyMainiTVF with ID: '

    + CAST (@ID AS VARCHAR (20))

    + '; and Name: ' + @Name AS RET_VAL

    GO

    /* CREATE TEST SET */

    DECLARE @TestTable TABLE (ID INT, Name VARCHAR (200))

    INSERT @TestTable VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')

    /* THE SELECT */

    SELECT

    XTV.RET_VAL

    FROM @TestTable TT

    CROSS APPLY dbo.MyMainiTVF(TT.ID,TT.Name,@@SPID) AS XTV

    Results

    RET_VAL

    -----------------------------------------------------------

    SessionID - 54 called MyMainiTVF with ID: 1; and Name: AAA

    SessionID - 54 called MyMainiTVF with ID: 2; and Name: BBB

    SessionID - 54 called MyMainiTVF with ID: 3; and Name: CCC

    SessionID - 54 called MyMainiTVF with ID: 4; and Name: DDD

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply