Stored Procedures

Introduction

Damsy ERP persists and retrieves data almost exclusively through SQL Server stored procedures. The application repository references procedure names as string literals inside DAL Procedure structs; the full schema, table definitions, and procedure bodies are not maintained in Git.

Understanding naming conventions and the DAL mapping pattern is essential for safe database changes and new feature work.

Purpose

This page documents how stored procedures are named, discovered in code, invoked, and coordinated with deployments — without pretending to be a complete SP catalog.

When to Use

Refer to this page when you:

• Add a new read or write operation to the database
• Search the codebase for an existing SP name before creating a duplicate
• Plan a DBA deployment alongside application changes
• Debug SqlException "could not find stored procedure"

How It Works

Naming conventions

Category	Prefix / pattern	Examples
Read (single/list)	Get*	[dbo].[GetLoginUser], [dbo].[GetEmployeeSearchPopupList]
Read (alternate)	GET* (legacy)	Some older procedures use uppercase
Write (upsert)	InsertUpdate*	[dbo].[InsertUpdateChangePassword]
Update only	Update*	[dbo].UpdateUserAccount
Delete	Delete*	Module-specific delete SPs
Permissions	GetData*	[dbo].[GetDataUserPermissions]
Configuration	Get*Config*	[dbo].[GetEmailConfigration]

Read procedures typically accept filter parameters (@CompID, @UserAccountsID, date ranges) and return result sets. Write procedures often return success via output parameters or row counts consumed by DAL as bool.

Procedure struct in DAL

Each DAL class colocates SP name constants in a private nested struct:

struct Procedure
{
    public static string SP_Read = "[dbo].[GetLoginUser]";
    public static string SP_Write = "[dbo].[InsertUpdateChangePassword]";
    public static string Sp_GetUserList = "[dbo].[GetDataUserPermissions]";
    public static string SP_UPDATEUSER = "[dbo].UpdateUserAccount";
    public static string Sp_GetMailConfiguration = "[dbo].[GetEmailConfigration]";
    public static string Sp_UserList = "[dbo].[GetAdminLoginList]";
}

Benefits:

• Single place to update if SP is renamed in SQL
• IntelliSense-friendly grouping per feature
• Clear read vs write separation

Invocation:

datatable = baseHl.GetDataTable(Procedure.Sp_GetUserList, Sp);

Schema not in Git

What is in Git:

• DAL method names and parameter lists (implicit contract)
• Procedure struct string literals
• Blank DB bootstrap scripts under database-backup/scripts-blank-db/ (minimal seed, not full ERP schema)

What is not in Git:

• Complete procedure source for all modules
• Full table/index DDL for production ERP databases
• Historical migration history

Coordinate DB and code releases

Deploying DAL that references a new SP name before the SP exists in SQL causes production failures. Deploy SP first, or ship simultaneously with a verified script.

Parameter contract

Parameters are defined in SQL Server and mirrored in C#:

cmd.Parameters.Add(new SqlParameter("@loginid", objUser.LoginID));
cmd.Parameters.Add(new SqlParameter("@userpassword", objUser.Password));
cmd.Parameters.Add(new SqlParameter("@IsActive", objUser.IsActive));
cmd.Parameters.Add(new SqlParameter("@compid", objUser.Compid));

Common parameters across modules:

Parameter	Source
@CompID / @compid	Session["CompID"]
@UserAccountsID	Session["UserID"]
@BranchCode	Session["BranchCode"]
@ModuleID, @MenuID	Permission queries

Discovering SP names in code

Search the solution for:

struct Procedure
[dbo].[Get
InsertUpdate

Module folders mirror DAL structure: DAL/Payroll/, DAL/Bills/, etc.

Developer Notes

• Brackets and schema: Most literals use [dbo].[ProcedureName] format; occasional legacy entries omit brackets on one side.
• Typos in SP names: Some database objects preserve historical misspellings (GetEmailConfigration, AttendancePuncging column). Match SQL exactly.
• Timeout: Long-running report SPs may require elevated CommandTimeout in DAL.
• Blank DB seed: Minimal scripts create core login tables and seed admin user — not every module SP.

Adding a new stored procedure (checklist)

1. Author and deploy SP in SQL Server (dev instance)
2. Grant execute permissions for app SQL login
3. Add constant to DAL Procedure struct
4. Implement DAL method with SqlParameter[]
5. Call from controller after session gate
6. Document parameter contract in your team's SQL script repo (outside this docs site if needed)

Examples

Login authentication SP

Item	Value
Name	[dbo].[GetLoginUser]
DAL	Login_DAL.GetLoginUser
Parameters	@loginid, @userpassword, @IsActive, @compid
Returns	User row with module flags and e-invoice configuration

Permission read SP

Item	Value
Name	[dbo].[GetDataUserPermissions]
DAL	Login_DAL (private GetDataUserPermissions)
Parameters	@UserAccountsID, @ModuleID, @MenuID

Write SP pattern

public bool SaveBranch(BranchMaster_BO bo)
{
    SqlParameter[] parms = {
        new SqlParameter("@CompID", bo.CompID),
        new SqlParameter("@BranchCode", bo.BranchCode),
        // ...
    };
    return baseHl.ExecuteProcedure(Procedure.SP_Write, parms);
}

Related Pages

• Data Access Layer
• Business Objects
• System Overview

Important Notes

No ORM migrations

There is no EF Code First migration path. Database evolution is SP/script driven.

Blank database scripts

See database-backup/scripts-blank-db/ in the ERP repository for minimal setup — not a substitute for production schema documentation.

Common Mistakes

Mistake	Result
Assuming SP exists because DAL references it	Empty dev DB → runtime error
Renaming SP in SQL only	DAL still calls old name
Missing @CompID filter in new SP	Cross-company data leak
Using dynamic SQL in controller instead of SP	Breaks pattern; security review failure
Copy-paste Procedure struct without renaming	Wrong SP executed silently

Next Steps

1. Read Data Access Layer for BaseHelper execution methods.
2. Use SSMS on your connected dev database as the source of truth for parameter lists and result sets.