Stored Procedure

LabKey Support Forum
Stored Procedure marcia hon  2018-03-22 06:54
Status: Closed
 

Hello,

I have a stored procedure that is supposed to work with assays.

How do I write the table in the stored procedure. I have tried: "FROM assay.General.Genotyping_Original.Data instrument;" but this gives me an error.

How can I, inside a stored procedures, connect to an existing assay?

Thanks,
Marcia

 
 
Jon (LabKey DevOps) responded:  2018-03-30 11:23
Hi Marcia,

Have you reviewed our docs on stored procedures with regard to the ETL?

https://www.labkey.org/Documentation/wiki-page.view?name=etlsproc
https://www.labkey.org/Documentation/wiki-page.view?name=etlStoredProcMSSQL
https://www.labkey.org/Documentation/wiki-page.view?name=etlstoredproccheck

If you are asking about how to tell the stored procedure to work with the specific assay, you would need to specific the entire assay schema.

For example, if you created a GPAT assay called "myGenericAssay", the Schema Browser would allow you to access the assay schema structure as:

Assay > General > myGenericAssay

This is also shown as:

"assay.General.myGenericAssay"

So for the stored procedure example we have in our documentation that appears like this, where "schemaName" represents the schema:

CREATE procedure [schemaName].[queryName]

You would use "assay.General.myGenericAssay" as the schema name. Below starts out creating the stored procedure for the Assay Data (aka Assay Results) table for the "assay.General.myGenericAssay" schema:

CREATE procedure [assay.General.myGenericAssay].[Data]

Regards,

Jon
 
marcia hon responded:  2018-04-03 06:36
I have tried to select/update assay.General.Genotyping_Original.Data instrument.data , however, Labkey does not recognize this.
 
Jon (LabKey DevOps) responded:  2018-04-07 23:35
Hi Marcia,

My apologies, I was thinking in LabKey SQL terms and not Microsoft SQL terms.

The "assay.General.myGenericAssay" schema is a virtual schema that our API and queries understand and work with.

A stored procedure would have to work with the actual table on the database itself. So a GPAT assay would look something like assayresult.c1761228512Genotyping_Original in the actual database. However, we really don't recommend going against the DB directly for these specific tables since this would require you to figure out what assay is tied to which specific container, which is difficult considering the special numeric sequence that is used in the table name.

If you're looking to transform the information that enters into an assay, in lieu of an ETL, you might want to consider using Transformation Scripts for the Assay instead (https://www.labkey.org/Documentation/wiki-page.view?name=programmaticQC).

Regards,

Jon
 
marcia hon responded:  2018-04-09 05:57
In my stored procedure, I have tried:

   get_instrument CURSOR FOR
   SELECT DISTINCT
    instrument."participant id",
    instrument."visit id",
    instrument."series name",
    instrument."file name",
    instrument."location"
    FROM assayresult.c190d2165_genotyping_original_data_fields instrument;

It compiles fine and the ETL runs "COMPLETE". However, the desired operation is not happening (I cannot move data from one table to another).