Stored procedure for insert into and select from tables
How to add the following to a stored procedure (Cache Studio)
1. Select from a few tables and insert result into a TABLEA.
2. Then select data from the TABLEA, apply some SQL logic, insert results into TABLEB.
3. SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
Comments
UNderstand a STORED PROCEDURE as a special case of a ClassMethod
using CREATE PRODURE .......LANGUAGE OBJECTSCRIPT
You can write it with all specials you need
see docs: https://docs.intersystems.com/iris20242/csp/docbook/Doc.View.cls?KEY=RSQL_createprocedure
Hi Robert, the webpage mentions version - InterSystems IRIS Data Platform 2024.2 = (2024)
The version I use is - InterSystems 2010.2.3 (Build 702) = (2010)
None of the examples shows how to use a temp table/normal table to insert into, then do a select from that temp/normal table.
............................................................................
If I try doing below:
CREATE PROCEDURE PersonStateSP()
BEGIN
SELECT Name,Home_State FROM Sample.Person ;
ENDI get error [Code: 25, SQL State: 37000] [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (END) encountered after end of query^Sample.Person END>]
.........................................................................................
Tried using a class (Cache Studio)
for stored procedure, but instead of returning 7 rows I only get 3 rows. Seems issue lies with the Fetch section
.......................................................................
I use it since Caché 2014.* at least.
this just the latest documentation.
What's the purpose of the semicolon ? SELECT Name,Home_State FROM Sample.Person ;
for TEMP table see: https://docs.intersystems.com/iris20242/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createtable#RSQL_createtable_desc_temp
Before collecting all pieces in the Stores Procedure
I'd suggest to test you queries isolated im SMP or from terminal in SQL shell
It gives same error with/without semicolon.
Queries work 100% from within DBVisualizer.
Seems issue lies with the Fetch section in Class ....ReportStoredProcs Extends %RegisteredObject
The insert works 100%, but below fetch only returns 3 rows instead of 7, if I remove Quit $$$OK , then I get 7 rows, but of course it returns an error
s rsFetchTempData = ##class(%ResultSet).%New()
s sc = rsFetchTempData.Prepare("SELECT * FROM TableA")
if 'sc q sc
s sc = rsFetchTempData.Execute()
if 'sc q sc
s qHandle = rsFetchTempData
{
s sc = qHandle.Next()
if (qHandle.Next())
{
// Fetch the current row
s Row = $LB(qHandle.Get("Col1"), qHandle.Get("Col2"), qHandle.Get("Col3"), qHandle.Get("Col4"), qHandle.Get("Col5"), qHandle.Get("Col6"), qHandle.Get("Col7"))
}
else
{
s AtEnd = 1
s Row = ""
}
Quit $$$OK
}
Your code looks like a ClassQuery. %Library.Query
This is not a Stored Procedure :=> a ClassMethod ...[SqlProc,SqlName=anyname]
and everything happens inside this ClassMethod
you run is by CALL PROCEDURENAME
Yes, reason for using it is because I can't created a stored procedure in the database via DBVisualizer.
Fixed the Fetch section, now the fetch returns 7 rows. So issue sorted.