Como crear un Stored Procedure

Cada SP debe existir en ambos motores de base de datos. Esta guia cubre la convencion de nombres, plantillas y el proceso de despliegue.

Convencion de nombres

usp_[Schema]_[Entity]_[Action]

Ejemplos:
  usp_Master_Product_List
  usp_Doc_Invoice_Create
  usp_AR_Payment_Apply
  usp_Cfg_Company_GetById

Schemas disponibles: cfg, sec, master, doc, ar, ap, acct, pay, pos, rest, hr, fin, sys, store, fiscal, inv, mfg, fleet, crm, log.

Patrones de salida

Plantilla SQL Server

CREATE OR ALTER PROCEDURE [schema].usp_Schema_Entity_Create
  @CompanyId   INT,
  @Name        NVARCHAR(100),
  @UserId      INT,
  @Resultado   INT OUTPUT,
  @Mensaje     NVARCHAR(500) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO [schema].[Entity] (CompanyId, Name, CreatedBy, CreatedAt)
    VALUES (@CompanyId, @Name, @UserId, SYSUTCDATETIME());

    SET @Resultado = SCOPE_IDENTITY();
    SET @Mensaje = N'Registro creado correctamente';

    COMMIT;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    SET @Resultado = -1;
    SET @Mensaje = ERROR_MESSAGE();
  END CATCH
END
GO

Plantilla PostgreSQL

CREATE OR REPLACE FUNCTION usp_schema_entity_create(
  p_company_id   INT,
  p_name         VARCHAR(100),
  p_user_id      INT
)
RETURNS TABLE("ok" BOOLEAN, "mensaje" VARCHAR, "id" INT)
LANGUAGE plpgsql AS $$
DECLARE
  v_id INT;
BEGIN
  INSERT INTO schema."Entity" ("CompanyId", "Name", "CreatedBy", "CreatedAt")
  VALUES (p_company_id, p_name, p_user_id, NOW() AT TIME ZONE 'UTC')
  RETURNING "Id" INTO v_id;

  RETURN QUERY SELECT true, 'Registro creado correctamente'::VARCHAR, v_id;
EXCEPTION WHEN OTHERS THEN
  RETURN QUERY SELECT false, SQLERRM::VARCHAR, -1;
END;
$$;

Tabla de traducciones SQL Server a PostgreSQL

Agregar a run_all.sql

Ambos motores tienen un archivo maestro que despliega todos los scripts:

-- SQL Server (web/api/sqlweb/run_all.sql)
:r includes/sp/usp_modulo.sql

-- PostgreSQL (web/api/sqlweb-pg/run_all.sql)
\i includes/sp/usp_modulo.sql

Llamar desde la API

La API usa helpers en web/api/src/db/query.ts:

// Lectura simple (SELECT)
const result = await callSp('usp_Schema_Entity_List', { CompanyId: 1 });

// Con parametros OUTPUT (listados con TotalCount)
const result = await callSpOut('usp_Schema_Entity_List',
  { CompanyId: 1, Page: 1, PageSize: 25 },
  ['TotalCount']
);

// Escritura con OUTPUT (Resultado + Mensaje)
const result = await callSpOut('usp_Schema_Entity_Create',
  { CompanyId: 1, Name: 'Test', UserId: 5 },
  ['Resultado', 'Mensaje']
);

Ejemplo completo: usp_Master_Category

Un SP de listado en ambos motores:

-- SQL Server
CREATE OR ALTER PROCEDURE master.usp_Master_Category_List
  @CompanyId  INT, @Page INT = 1, @PageSize INT = 25,
  @TotalCount INT OUTPUT
AS BEGIN
  SET NOCOUNT ON;
  SELECT @TotalCount = COUNT(*) FROM master.Category WHERE CompanyId = @CompanyId;
  SELECT Id, Name, Active
  FROM master.Category WHERE CompanyId = @CompanyId
  ORDER BY Name
  OFFSET (@Page - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
END
GO

-- PostgreSQL
CREATE OR REPLACE FUNCTION usp_master_category_list(
  p_company_id INT, p_page INT DEFAULT 1, p_page_size INT DEFAULT 25
)
RETURNS TABLE("Id" INT, "Name" VARCHAR, "Active" BOOLEAN, "TotalCount" BIGINT)
LANGUAGE plpgsql AS $$
DECLARE v_total BIGINT;
BEGIN
  SELECT COUNT(*) INTO v_total FROM master."Category" WHERE "CompanyId" = p_company_id;
  RETURN QUERY
    SELECT c."Id", c."Name", c."Active", v_total
    FROM master."Category" c WHERE c."CompanyId" = p_company_id
    ORDER BY c."Name"
    LIMIT p_page_size OFFSET (p_page - 1) * p_page_size;
END;
$$;