KB37783: Instructions to use stored procedures within Freeform SQL reports in MicroStrategy against different databases

Greenplum

CREATE FUNCTION emp_search(int) RETURNS SETOF emp AS $$

    SELECT emp_id, emp_first_name||' '||emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = $1;

$$ LANGUAGE SQL;
select * from emp_search(5);

delete from emp;

Infobright (using MSTR MySQL driver)

CREATE PROCEDURE emp_search (

IN DistCtrID INT)

BEGIN

      SELECT emp_id, CONCAT(emp_first_name, ' ', emp_last_name)

      FROM lu_employee

      WHERE dist_ctr_id = DistCtrID;

  END;

CALL emp_search()

 
 Informix Dynamic Server

 
 Informix Dynamic Server

create  procedure  emp_search3  (DistCtrID int)  returning  int  ,char(20); 

define  v1  int; 

define  v2  char(20); 

foreach  select  emp_id, emp_first_name||' '||emp_last_name into  v1,v2  from lu_employee where  dist_ctr_id=DistCtrID

return  v1,v2  with  resume; 

end  foreach; 

end  procedure;

execute procedure emp_search3(5)

Microsoft SQL Server

CREATE PROCEDURE emp_search

@DistCtrID INT

as

    SET NOCOUNT ON;

    SELECT emp_id, emp_first_name + ' ' + emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = @DistCtrID;

exec emp_search @DistCtrID=

MySQL Community Server and

MySQL Enterprise

CREATE PROCEDURE emp_search (

IN DistCtrID INT)

BEGIN

      SELECT emp_id, CONCAT(emp_first_name, ' ', emp_last_name)

      FROM lu_employee

      WHERE dist_ctr_id = DistCtrID;

END;

CALL emp_search()

Netezza

create table emp_name (emp_id integer, emp_name varchar(100));

CREATE OR REPLACE PROCEDURE emp_search (int) RETURNS REFTABLE(emp_name) LANGUAGE NZPLSQL AS

BEGIN_PROC

DECLARE

distid alias for $1;

BEGIN

execute immediate 'INSERT INTO ' || REFTABLENAME || ' select emp_id,emp_first_name || '' ''||emp_last_name from lu_employee where dist_ctr_id ='||distid;

RETURN REFTABLE;

END;

END_PROC;

CALL emp_search(5)

ParAccel

CREATE FUNCTION emp_search(int) RETURNS SETOF emp AS $$

    SELECT emp_id, emp_first_name||' '||emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = $1;

$$ LANGUAGE SQL; 

select * from emp_search(5);

delete from emp; 

PostgreSQL

CREATE FUNCTION emp_search(int) RETURNS SETOF emp AS $$

    SELECT emp_id, emp_first_name||' '||emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = $1;

$$ LANGUAGE SQL;

select * from emp_search(5);

delete from emp;

Sybase ASE

CREATE PROCEDURE emp_search

@DistCtrID INT

as

    SET NOCOUNT ON

    SELECT EMP_ID, EMP_FIRST_NAME + ' ' + EMP_LAST_NAME

    FROM LU_EMPLOYEE

    WHERE DIST_CTR_ID = @DistCtrID; 

exec emp_search @DistCtrID=

 Sybase IQ

CREATE PROCEDURE emp_search (

IN DistCtrID INT)

BEGIN

    SELECT emp_id, emp_first_name + ' ' + emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = DistCtrID;

  END;

CALL emp_search()

Teradata

create macro emp_search as ( DistCtrID integer )

as (

   SELECT emp_id, emp_first_name||' '||emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = :DistCtrID;

); 

exec emp_search (5)

IBM DB2 UDB for iSeries

CREATE PROCEDURE emp_search(IN DistCtrID INTEGER) LANGUAGE SQL

DYNAMIC RESULT SETS 1

BEGIN

  DECLARE c1 CURSOR FOR

    SELECT emp_id, emp_first_name||' '||emp_last_name

    FROM lu_employee

    WHERE dist_ctr_id = DistCtrID;

OPEN c1;

return;

END

call emp_search())