Store Procedure - Codenull

Using Store Procedures

Sometimes you need some data that requires executing complex queries; for that, you may want to use stored procedures in the SQL Database

1. GraphQL Query Signature

ExecStoreProcedure(name: String!, params: JSON, limit: Int, offset: Int): JSON

2. Parameters

name (required): Name of the stored procedure
params (optional): JSON object with stored procedure parameters
limit (optional): Result limit (can be passed directly or inside params).
offset (optional): Offset for pagination (can be passed directly or inside params)

If you are using this data source in the Datagrid component, it will send the

limit and

offset automatically. You only need to implement the pagination on the store procedure side.

The order of parameters in params matters, as they are passed positionally.

Usage Examples:

Example 1: With parameters and separate pagination

query {
  ExecStoreProcedure(
    name: "GetUsers"
    params: { status: "active", role: "admin" }
    limit: 10
    offset: 0
  )
}

Generates: EXEC GetUsers 'active', 'admin', 10, 0 Example 2: With pagination inside params

query {
  ExecStoreProcedure(
    name: "GetUsers"
    params: { status: "active", limit: 10, offset: 0 }
  )
}

Generates: EXEC GetUsers 'active', 10, 0 Example 3: Only with limit and offset as separate parameters

query {
  ExecStoreProcedure(
    name: "GetAllUsers"
    limit: 20
    offset: 10
  )
}

Generates: EXEC GetAllUsers 20, 10 Example 4: Send params as a variable

query IngresosVsEgresosPorPersona($params: JSON, $limit: INT, $offset: INT) 
{ 
    ExecStoreProcedure(name: "[dbo].[getIngresosVsEgresosPorPersona]", 
                        params: $params, limit: $limit, offset: $offset)
}

Datasource Variables: we need to put the variables (parameters, limit, and offset) that we will send to the SP example variable list:

{
      "type": "nested",
      "name": "params",
      "variables": [
        {
          "type": "session",
          "value": "user.Id",
          "name": "Id"
        },
        {
          "type": "context",
          "value": "tipoConcepto",
          "name": "tipoConcepto",
          "defaultValue": null
        }
      ]
    }

Complete example:

{
  "query": "query IngresosVsEgresosPorPersona($params: JSON) { \n  IngresosVsEgresosPorPersona: ExecStoreProcedure(name: \"[dbo].[getIngresosVsEgresosPorPersona]\", params: $params)\n}\n",
  "variables": [
    {
      "type": "nested",
      "name": "params",
      "variables": [
        {
          "type": "session",
          "value": "user.Id",
          "name": "Id"
        },
        {
          "type": "context",
          "value": "tipoConcepto",
          "name": "tipoConcepto",
          "defaultValue": null
        }
      ]
    }
  ],
  "path": "IngresosVsEgresosPorPersona",
  "fetchPolicy": "cache-and-network"
}

1. Stored Procedure in SQL Server

CREATE PROCEDURE GetUsersPaginated
    @status NVARCHAR(50) = NULL,
    @role NVARCHAR(50) = NULL,
    @limit INT = 10,
    @offset INT = 0
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Query with pagination using OFFSET and FETCH NEXT
    SELECT 
        Id,
        Name,
        Email,
        Status,
        Role,
        CreatedAt
    FROM Users
    WHERE 
        (@status IS NULL OR Status = @status)
        AND (@role IS NULL OR Role = @role)
    ORDER BY CreatedAt DESC
    OFFSET @offset ROWS
    FETCH NEXT @limit ROWS ONLY;
    
    -- Optional: Return total count for pagination metadata
    SELECT COUNT(*) AS TotalCount
    FROM Users
    WHERE 
        (@status IS NULL OR Status = @status)
        AND (@role IS NULL OR Role = @role);
END
GO

GraphQL Call

query {
  ExecStoreProcedure(
    name: "GetUsersPaginated"
    params: { 
      status: "active"
      role: "admin"
    }
    limit: 20
    offset: 0
  )
}

2. More Complete Example with Multiple Parameters

CREATE PROCEDURE SearchProducts
    @categoryId INT = NULL,
    @minPrice DECIMAL(10,2) = NULL,
    @maxPrice DECIMAL(10,2) = NULL,
    @searchTerm NVARCHAR(100) = NULL,
    @limit INT = 10,
    @offset INT = 0
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        ProductId,
        ProductName,
        Description,
        Price,
        CategoryId,
        Stock
    FROM Products
    WHERE 
        (@categoryId IS NULL OR CategoryId = @categoryId)
        AND (@minPrice IS NULL OR Price >= @minPrice)
        AND (@maxPrice IS NULL OR Price <= @maxPrice)
        AND (@searchTerm IS NULL OR ProductName LIKE '%' + @searchTerm + '%')
    ORDER BY ProductName
    OFFSET @offset ROWS
    FETCH NEXT @limit ROWS ONLY;
END
GO

Graphql Call

query {
  ExecStoreProcedure(
    name: "SearchProducts"
    params: {
      categoryId: 5
      minPrice: 10.00
      maxPrice: 100.00
      searchTerm: "laptop"
    }
    limit: 25
    offset: 0
  )
}

​Using Store Procedures

​1. GraphQL Query Signature

​2. Parameters

​Usage Examples:

​1. Stored Procedure in SQL Server

​GraphQL Call

​2. More Complete Example with Multiple Parameters

​Graphql Call

Using Store Procedures

1. GraphQL Query Signature

2. Parameters

Usage Examples:

1. Stored Procedure in SQL Server

GraphQL Call

2. More Complete Example with Multiple Parameters

Graphql Call