Stored Procedures the Swiss army knife of SQL

One of the reasons why I love Cache and Iris is that not only you can do anything you can imagine, also you can do it in a lot of different ways!!.

Imagine that you have an integration running with IRIS connected by ODBC you probably only run SQL queries but you can also create stored procedures and inside write the code to do everything you can imagine.

I'm going to give you some examples but the limit is your imagination!!

You might wonder, wait!! I don't know how to create a store procedure in IRIS. But this it's not true, you know how to create them but you don't know yet!.

Create a stored procedure is simply add a tag to your ClassMethod like this:

ClassMethod testStoredProcedure(name As %String) As %String [ SqlProc ]
{
    Quit "hello "_name
}

 Only adding the tag SqlProc to your ClassMethod definition you can use it as a stored procedure.

And for call it just make a call like this:

Select AQS_urlShortener.Url_testStoredProcedure('Dani')

In case that you aren't interested in the answer you can simply make a call to the stored procedure

Call AQS_urlShortener.Url_testStoredProcedure('Dani')

And the best thing about this is that you can use it for anything you want, examples:

1 - Use a stored procedure to insert a bunch of data much faster than with queries.

Imagine you have composed a registry like this:

"customerName|address|phone¬customer2Name|address|phone¬customer3Name|address|phone...."  

and you have this stored procedure:

ClassMethod StoreCustomers(registers As %String(MAXLEN=36000)) As %String [ SqlProc ]
{
    #Dim regX, name, address, phone, i, customerId, counter

    Set counter = 0
    For i=1:1:$LENGTH(registers,"¬")
    {
        Set regX=$Piece(registers,"¬",i)
        Continue:regX=""
        Set name    = $PIECE(regX,"|",1)
        Set address = $PIECE(regX,"|",2)
        Set phone   = $PIECE(regX,"|",3)

        Set customerId = $INCREMENT(^Customers)
        Set ^Customers(customerId,"Name")   = name
        Set ^Customers(customerId,"Address")= address
        Set ^Customers(customerId,"Phone")  = phone
        Set counter = counter + 1
    }


    Quit "Stored "_counter_" customers"
}

You can store customers in insane speed..

(Pay attention to the definition of the method's parameter, I have added (MAXLEN = X) where X is the MAXLENGTH value for strings that you have configured.)

After that you can call the stored procedure like this for example

Select AQS_urlShortener.Url_StoreCustomers('Dani|C/Falsa,123|+34614258966¬Robert|Wall Street, 123|+54856325774')

(One thing to keep in mind is that in this case you also have to control the length of the stored procedure call so that you don't receive MAXSTRING error by splitting into several stored procedure calls if you want to record many clients at once)

I know it's not very pretty but it's really fast and it could safe you on more than one occasion. 

2 - Take advantage of an existing development:

You might have a development made for send SMS to your customer and you want take advantage in an external development but you want do it faster. Ok, you only have to create one stored procedure and call it via ODBC

ClassMethod SendSmsToCustomer(phone As %String, message As %String(MAXLEN=2000)) As %String [ SqlProc ]
{
    #Dim result

    Set result = ##class(MySmsClassSender).send(phone, message)


    Quit result
}

And here the result:

3 - Execute a bunch of beloved routine and class methods:

ClassMethod ExecuteRoutineX(customerId As %Integer, phone As %String, address As %String, message As %String(MAXLEN=2000)) As %String [ SqlProc ]
{
    #Dim result

    Do SavePhoneCustomer^MyRoutine(customerId, phone)
    Do SaveAddressCustomer^MyRoutine(customerId, address)
    Do ##class(MySmsClassSender).send(phone, message)

    Quit "Customer Stored"
}

In the end, the limit is in your imagination!

Oh!, to explore your stored procedures created in the portal, you can go to:

System Explorer -> SQL 

Now you can use Stored Procedures too!!

I hope you find this article useful,  Thanks for reading!!