Cacher is the code snippet organizer for pro developers

--Author: Bakhtiar Abdullah (backids99@gmail.com)
CREATE PROCEDURE dbo.validation
    @tableName VARCHAR(100),
    @ids VARCHAR(MAX)
AS 

DECLARE @banyak INT,
        @i INT,
        @vField VARCHAR(100),
        @vCondition VARCHAR(1000),
        @vMessage VARCHAR(1000),
        @query VARCHAR(1000)

DECLARE @tabel TABLE (
    [id] INT IDENTITY(1,1),
    [fieldName] VARCHAR(100),
    [condition] VARCHAR(1000),
    [errorMessage] VARCHAR(1000)
)

--CREATE TABLE Antasena.dbo.parameterValidation (
--	id int IDENTITY(0,1) NOT NULL,
--	tableName varchar(100) NOT NULL,
--	fieldName varchar(100) NOT NULL,
--	[condition] varchar(100) NOT NULL,
--	errorMessage varchar(255) NOT NULL,
--	CONSTRAINT parameterValidation_PK PRIMARY KEY (id)
--)
--CREATE NONCLUSTERED INDEX parameterValidation_id_IDX ON dbo.parameterValidation (  id ASC  )  
--	WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
--	ON [PRIMARY ]

--CREATE TABLE Antasena.dbo.errorLog (
--	id int NULL,
--	tableName varchar(200) NULL,
--	reportName varchar(200) NULL,
--	columnName varchar(100) NULL,
--	dataError varchar(200) NULL,
--	error varchar(4000) NULL,
--	errorDate datetime NULL
--)

DELETE FROM Antasena.dbo.errorLog WHERE tableName = @tableName

INSERT INTO @tabel
    SELECT [fieldName], [condition], [errorMessage]
        FROM Antasena.dbo.parameterValidation
        WHERE [tableName] = @tableName

SET @i      = 1;
SET @banyak = (SELECT COUNT(0) FROM @tabel)

-- looping
WHILE (@i <= @banyak)
BEGIN
    
    SET @vField     = (SELECT [fieldName] FROM @tabel WHERE id = @i)    
    SET @vCondition = (SELECT [condition] FROM @tabel WHERE id = @i)
    SET @vMessage   = (SELECT [errorMessage] FROM @tabel WHERE id = @i)
        
    -- conditioning   
    IF (@ids IS NULL)
        BEGIN        
            SET @query = 'SELECT id, ''' + @tableName + ''' AS tableName, ''' + @vField + ''' AS columnName, ''' 
                        + @vMessage + ''' AS error, GETDATE() as errorDate FROM ' + @tableName + ' WHERE ' + @vCondition
        END
    ELSE
        BEGIN
            -- using id or idPelapor
            SET @query = 'SELECT id, ''' + @tableName + ''' AS tableName, ''' + @vField + ''' AS columnName, ''' 
                        + @vMessage + ''' AS error, GETDATE() as errorDate FROM ' + @tableName + ' WHERE (' + @vCondition + ') AND id IN (' 
                        + @ids + ')'
        END
    
    -- inserting to errorLog
    INSERT INTO Antasena.dbo.errorLog ([id], [tableName], [columnName], [error], [errorDate])
        EXEC (@query)
        
    SET @i = @i + 1
END