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.
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 limitandoffset 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
)
}
EXEC GetUsers 'active', 'admin', 10, 0
Example 2: With pagination inside params
query {
ExecStoreProcedure(
name: "GetUsers"
params: { status: "active", limit: 10, offset: 0 }
)
}
EXEC GetUsers 'active', 10, 0
Example 3: Only with limit and offset as separate parameters
query {
ExecStoreProcedure(
name: "GetAllUsers"
limit: 20
offset: 10
)
}
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)
}
{
"type": "nested",
"name": "params",
"variables": [
{
"type": "session",
"value": "user.Id",
"name": "Id"
},
{
"type": "context",
"value": "tipoConcepto",
"name": "tipoConcepto",
"defaultValue": 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
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
)
}
Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the ask query parameter:
GET https://codenull.gitbook.io/dev/configurations/components/datasources/store-procedure.md?ask=<question>