none
limit to number of rows to pass to SP with table value parameter RRS feed

  • Question

  • HI,

    I have created the SP with table value as parameter. Its being called from UI where multiple rows will be sent.

    Just want to know if there is any limit for the number of rows to pass to the SP(which has table  value as parameter)while executing it?

    In the table which I am using one of the column is XML data type.

    Please suggest.

    Thanks.


    Porus

    Wednesday, February 29, 2012 1:50 PM

Answers

All replies

  • Hi

    As far as I know there is no such limit. The size of table-valued parameters is limited only by server memory. Please read this link

    http://msdn.microsoft.com/en-us/library/bb675163.aspx

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Wednesday, February 29, 2012 1:58 PM
    • Proposed as answer by Naomi NModerator Wednesday, March 7, 2012 1:37 AM
    • Marked as answer by KJian_ Wednesday, March 7, 2012 5:18 AM
    Wednesday, February 29, 2012 1:55 PM
  • I don't think their was any limitations

    http://uk.linkedin.com/in/ramjaddu

    Wednesday, February 29, 2012 1:56 PM
  • Just want to know if there is any limit for the number of rows to pass to the SP(which has table  value as parameter)while executing it?

    Since you worry about, think of the alternate of uploading the data into a table and let the stored procedure process it from there.

    Table-valued parameter article:

    http://www.sqlusa.com/bestpractices2008/table-valued-parameter/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Tuesday, March 6, 2012 12:44 PM
    Moderator
  • I just tried something like this:

    CREATE TYPE dbo.MyTableTypeWithCheck AS TABLE
    (
        ID INT IDENTITY PRIMARY KEY
      , A  VARCHAR(10)
      , CHECK ( ID >= 1 AND ID <= 15000 )
    )
    GO
    
    DECLARE @MyTable AS dbo.MyTableTypeWithCheck
    INSERT INTO @MyTable ( A )
    SELECT LEFT(a1.name, 10)
    FROM sys.all_objects a1
    cross join sys.all_objects a2
    GO
    
    CREATE TYPE dbo.MyTableTypeNoCheck AS TABLE
    (
        ID INT IDENTITY PRIMARY KEY
      , A  VARCHAR(10)
    )
    GO
    
    DECLARE @MyTable AS dbo.MyTableTypeNoCheck
    INSERT INTO @MyTable ( A )
    SELECT LEFT(a1.name, 10)
    FROM sys.all_objects a1
    cross join sys.all_objects a2
    GO

    and it appeared to work. It might seem like a bit of a hack. May not meet the bar :)

    -- cheers!

    Tuesday, January 2, 2018 7:41 PM