Determine Length of Column and assign value using Case statement in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

when i ran the stored procedure, and i get an error said

Msg 102, Level 15, State 10, Procedure QAllAgencyPrograms_sp, Line 68 [Batch Start Line 7]Incorrect syntax near ','.

SELECT
PROGRAMS.AutoNum,
PROGRAMS.OrgCode,
PROGRAMS.OPAcadYearOnly,
LEN(PROGRAMS.OPPubDescrip > 400, 2, 1) AS number,
PROGRAMS.OPMissionS
FROM ORGANIZATIONS
    INNER JOIN PROGRAMS
        ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode
WHERE ORGANIZATIONS.Status = 'Approved'
AND   PROGRAMS.OPStatus = 'Active' 
ORDER BY ORGANIZATIONS.OrgCode

i have a stored procedure, and the table data field quite large, and i want to limit it.

if the field data length greater than 400 and automatically enter number "2" into the number column.

if the field data length less than 400 and automatically enter number "1" into the number column.

but i got error when i debug.  

Incorrect syntax near ','.

please help.

Posted 2 months ago

Hi phonghue,

Check this example.

SQL

SELECT
PROGRAMS.AutoNum,
PROGRAMS.OrgCode,
PROGRAMS.OPAcadYearOnly,
CASE WHEN LEN(PROGRAMS.OPPubDescrip) > 400 THEN 2
     ELSE 1 
END AS number,
PROGRAMS.OPMissionS
FROM ORGANIZATIONS
    INNER JOIN PROGRAMS
        ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode
WHERE ORGANIZATIONS.Status = 'Approved'
AND   PROGRAMS.OPStatus = 'Active'
ORDER BY ORGANIZATIONS.OrgCode