[SOLVED] How to update a SQL column in a stored procedure if the column was added?

How to update a SQL column in a stored procedure if the column was added?

I have a stored procedure where I select records from a table and I added a column for a flag (ReviewedFlag).

If I need to update this field how can I do it outside of the stored procedure? I need to do this so I can pick up records in future query where the records are flagged as 1 under the ReviewedFlag.

ALTER PROCEDURE [dbo].[spHRLaserficheRetirementsResignationsImport]
    @EmployeeID VARCHAR(MAX) = '0',
    @Flag VARCHAR(MAX) = 'PF'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @PackageCmd varchar(2000)
    DECLARE @FileName   varchar(255)
    DECLARE @TodayDate  varchar(2000)
    DECLARE @script     varchar(255)

    SELECT @TodayDate =  CONVERT(varchar(12), GETDATE(), 110) + '_' + REPLACE(CONVERT(varchar(12), GETDATE(), 108), ':', '')

    PRINT @TodayDate

    IF @FLAG = 'PF'
    BEGIN
        SELECT 
            -- rr.*
            0 AS ReviewedFlag,
            rr.EmployeeID AS EmployeeID,
            rr.SubmissionType AS SubmissionType,
            rr.TransID, rr.InstanceID AS InstanceID,

When I execute the stored procedure I get all of the records and a new column ReviewedFlag is added; however how can I update the flag for an individual record.

The update would change the flag from 0 to 1 that way we know it was reviewed on the backend.

Solution

You could add a parameter to the stored procedure to identify the records you want to have 1 for the ReviewedFlag. Maybe a ReviewedCriteria varchar. Then use that criteria to query for the PKs to return with 1 via CASE when creating the results.

ALTER PROCEDURE [dbo].[spHRLaserficheRetirementsResignationsImport]

--DECLARE
     @EmployeeID VARCHAR(MAX) = '0'
    ,@Flag VARCHAR(MAX) = 'PF'
    ,@ReviewedCriteria VARCHAR(MAX)=''
    --,@ReviewFlag  VARCHAR(MAX) = '0'

...
    @SQL= "SELECT pkItemToFlag  INTO #PksToFlagAsReviewed FROM ...
    WHERE" + @ReviewedCriteria 
    EXEC @SQL

...
SELECT 
       --rr.*
      CASE WHEN pk in (SELECT pkItemToFlag FROM PksToFlagAsReviewed ) THEN
1 as ReviewedFlag
ELSE 0 END as ReviewedFlag,**
       rr.EmployeeID as EmployeeID,
       rr.SubmissionType as SubmissionType,
     rr.TransID, rr.InstanceID as InstanceID,