• Replies 6 replies
  • Subscribers 42 subscribers
  • Views 1389 views
  • Users 0 members are here
Options
Related

How do I pass back data from a SQL Procedure

srmorrison@earthlink.net
Offline

Community,

I have successfully created a heavy SQL procedure that extracts all the xClassProperty Values from an End Item Assembly so that the client can:

1) Check which xClasses do not have xProperties assigned as we build out the XClassification tree

2) Check which parts in the End Item structure are not classified

3) Check which xProperty Values fields are missing values.

4) Validate the xProperty Values in the structure against a derived End Item Product Nomenclature (Translated Product Number)

5) Etc.

It is triggered from a Itemtype Report Javascript.

I build a table that contains all the xClasses, xClassProperties and their values for the entire End Item Structure in about 13 seconds.

The issue I'm have is that I can't return a string (table Name) back to the javascript method.

   [BomStruXPropValues_3E0619CA0A7B4999A2408FDE1600599E]

In SQL Server the way you declare a variable to pass back data is via the OUTPUT clause.

CREATE PROCEDURE [rh_GetBomStructureXPropValues](@structureGuidList nVarchar(max), @ReturnTableName  nVarchar(128) OUTPUT)

AS

The problem is that the Innovator SQL infrastructure see's the OUPUT clause as an Argument (ARG) instead of output (return) variable.

But what is pasted back is the collection of xp.XPropertyValues Table Names (from a tempDB table) that I create in the beginning of the to do dynamic queries against.

So here are my Questions that I need help with:

1) How do I get the Procedure to pass the table name to the javascript method via xPropValueResult?

2) How does the javascript variable xPropValueResult end up with the temdb values it?

Thanks for the guidance.

Scott M.

  • Offline

    Did you try

    xPropValueResult.output.ReturnTableName

  • Offline in reply to kaftab

    No I did not but will right now.

    Thanks

  • Offline in reply to [email protected]

    That did not work.

    Instead of using Set to assign the Table Name variable I also tried using Select and that did not work either.

    Thanks

  • Offline in reply to [email protected]

    Community,

    I did figure out a way to pass the table name to the javascript method.

    I don't know why this works but ....

    So, if anyone knows why this works or a better way to do it please let me know.

    Thanks

    I was able to answer Question 2:

    2) How does the javascript variable xPropValueResult end up with the temdb values it?

      Well its because of the following:

      - I created a temp table placing the XPropertyValues Table Names in it.

      - Then to Troubleshoot I did a Select



    Select * From #xPropTableListTempTable

    When testing resulting data showed up in the Results window SSMS.

    But when running the procedure from javascript it is some how passed within an item in the results

    So now Question 1:

    1) How do I get the Procedure to pass the table name to the javascript method via xPropValueResult?

     - I created a temp table placing the Table Name in it.

     - Commented out the Select in in Question 2

     - Placed a Select Clause for the new temp table and it was added to the item passed with the Results

     - All tempdb tables are dropped at the end.

    Here is the javascript:

  • in reply to [email protected]

    Hi,

    I haven´t read the complete thread, so excuse if my answer doesn´t make sense. But I wonder why you use an OUTPUT in a SQL procedure.

    In my case I use a final SELECT in the procedure to return the content I want. Inside the C# Method in Innovator I then use the result like any normal Innovator item.

    @kaftab: I never have seen your variant, but it looks interesting. Where did you find this one?

  • in reply to AngelaIp

    Edit: Out of interest I did a quick check and noticed that I did something like this last year (and I don´t remember it at all). But Procedure looks like this:

    CREATE PROCEDURE xyz
    @ID int,
    @debugMode BIT,
    @Output int out
    AS
    BEGIN
    [...]
    -- Return amount of affected rows to calling server Method
    SELECT @Output
    [...]

    END


    But in my case I didn´t use the inbuild SQL Process Method call, cause it was a federation use case. But I assume you should be apply to get the result with getItemByIndex or similiar.