> ## Documentation Index
> Fetch the complete documentation index at: https://docs.codenullapp.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Store Procedure

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

### 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

```graphql theme={null}
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)

<Check>
  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.
</Check>

<Warning>
  The order of parameters in params matters, as they are passed positionally.
</Warning>

#### **Usage Examples:**

**Example 1**: With parameters and separate pagination

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

Generates: `EXEC GetUsers 'active', 'admin', 10, 0`&#x20;

**Example 2**: With pagination inside params

```graphql theme={null}
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

```graphql theme={null}
query {
  ExecStoreProcedure(
    name: "GetAllUsers"
    limit: 20
    offset: 10
  )
}
```

Generates: `EXEC GetAllUsers 20, 10`&#x20;

**Example 4**: Send params as a variable

```graphql theme={null}
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:*

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

Complete example:

```graphql theme={null}
{
  "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

```sql theme={null}
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

```graphql theme={null}
query {
  ExecStoreProcedure(
    name: "GetUsersPaginated"
    params: { 
      status: "active"
      role: "admin"
    }
    limit: 20
    offset: 0
  )
}
```

#### 2. More Complete Example with Multiple Parameters

```sql theme={null}
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

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