Announcement

Collapse
No announcement yet.

Sending a "Table" Parameter to Stored Procedure

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sending a "Table" Parameter to Stored Procedure

    Perhaps this is only allowed within MS SQL itself, but SQL does support sending a table valued parameter to a stored procedure.

    Refer to:

    https://docs.microsoft.com/en-us/sql...ql-server-2017

    Do any of you send a variable number of parameters to a SQL stored procedure from PB code (a PB array for example)? I am using SQL Tools and I have a need to send a list of INTs to a stored proc. List will have a different number of elements each time I call the stored proc. I could put the list into a disk file and have the stored proc fetch them from the disk, but it won't be a long list so I was hoping for some kind of solution where I could include them in the call signature. Also, it won't just be a few element, so too many to declare a bunch of fixed parms.

    Any thoughts or suggestions would be most-welcome!


  • #2
    I didn't know that MS SQL supported that, I haven't run across it before.

    Given the phrase "table valued parameters" from that MS link... apparently the MS SQL ODBC driver does support it, see https://docs.microsoft.com/en-us/sql...ql-server-2017

    It would require the use of a new, nonstandard datatype called SQL_SS_TABLE that is (apparently) unique to MS SQL, so SQL Tools isn't going to know what to do with it. But if you can find a numeric value for %SQL_SS_TABLE (no luck so far) you may then be able to use %SQL_C_DEFAULT on the SQL Tools end.

    -- Eric Pearson, Perfect Sync
    "Not my circus, not my monkeys."

    Comment


    • #3
      Cool, thanks for the tip Eric!

      I'll dig into that and post back if I find something.

      Comment


      • #4
        Found it! Use %SQL_SS_TABLE = -153 for the BASIC Datatype in SQL_BindParameter, and give it a try. No clues, however, about the type of array you'll need to use. A LONG array is a good guess.
        "Not my circus, not my monkeys."

        Comment


        • #5
          Wow, me too...at the same time. But you posted first so props to you. Thanks! Now to figure out some code to make it work.

          Comment


          • #6
            I was finally was able to get some time to dig into this.

            Here is a link to the best instructions I found for sending a TVP to a stored proc via ODBC:
            https://docs.microsoft.com/en-us/sql...ql-server-2017

            There are some good examples at this link (I focused on "demo_fixed_TVP_binding"):
            https://docs.microsoft.com/en-us/sql...ql-server-2017

            Information at the first link illustrates the use of a (external to SQL) memory array to send the TVP data all at once, referred to as "Fixed TVP Row Binding" (versus "Variable TVP Row Binding"), which is suitable for my purposes. Regardless of the fixed/variable method, there are more steps to the binding process then are required for simple scalar parameters, and these steps are described quite clearly at the first link and examples found there and at the second link. Basically, you bind the TVP, then you bind each of the "columns" of the TVP individually.

            I did my best to implement this in PB and everything executed with %SQL_Success return values...right up to the STMT %EXECUTE part, which failed with a "COUNT field incorrect or syntax error".

            In the examples at the links, they call SQLSetStmtAttr() and is has a fourth parameter which they send in %SQL_IS_INTEGER. SQL Tools command SQL_SetStmtAttrib() (as well as the verbose option) do not have that parameter, so not sure what SQL Tools does about that, but I did get %SQL_Success as a return value when calling it both times.

            I don't have any more time to put to this right now so I am going to use a different option for the time being, which is working but is less desirable. Once I meet my deadline, I hope to be able to return to this and post some working code.

            Comment

            Working...
            X