SQL Error while UPDATE/SET that contains CASE

See more:

SQL

Hello Thank you for your help. I have commented an UPDATE statement about 3/4 of the way down. The update statement is producing an error that I am sure has something to do with the Declaration and Size of the data field but I am simply not understanding it. I've tried giving the declaration and the convert function a size of (20) and (max) but the error still persists. I am not a real SQL programmer. Help will be greatly appreciated. Also, I am interested in any comments regarding design of this query/update. One of the problems I have with coding is always knowing there has to be a better, more efficient way, and that always bothers me! :)

ERROR MESSAGE:
Msg 8152, Level 16, State 14, Line 70
String or binary data would be truncated.
The statement has been terminated.

TABLE STRUCTURE:

USE [TestExcelQuery]
GO

/****** Object:  Table [dbo].[tbl_FocusAgents]    Script Date: 06/05/2014 10:42:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_FocusAgents](
    [idx] [float] NULL,
    [AgyCode] [float] NULL
) ON [PRIMARY]

GO

ERRONEOUS SQL:

DECLARE @i int
DECLARE @numrows int
DECLARE @agency_id int
DECLARE @agency_code varchar(max)
DECLARE @agency_entire_code varchar(max)
DECLARE @AssociatedAgencyCodes varchar(max)

DECLARE @agency_name varchar(max)
DECLARE @agency_lob varchar(max)
DECLARE @agency_table TABLE 
    (
        idx smallint Primary Key IDENTITY(1,1)
        ,agency_id varchar(max)
        ,agency_name varchar(max)
        ,agency_lob varchar(max)
        ,AssociatedAgencyCodes varchar(max)
    )
-- populate temp agency table
INSERT INTO @agency_table
SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
        'Excel 8.0;Database=C:\MSSQLTips\Agents.xls;', 
        'SELECT AgyCode, AgencyName, LineofBusiness, AssociatedAgencyCodes  FROM [Sheet1$]')
-- enumerate the table
Select * from @agency_table
SET @agency_entire_code = CONVERT(VARCHAR(max), '000') + CONVERT(VARCHAR(max), @agency_code);
SET @i = 0
SET @numrows = (SELECT COUNT(*) FROM @agency_table)
IF @numrows > 0
    --Begin loop through table - print outputs commented
    WHILE (@i <= (SELECT MAX(idx) FROM @agency_table))
        BEGIN
        --PRINT 'The counter : ' + CAST(@i AS CHAR)
        SET @agency_id = (SELECT agency_id FROM @agency_table WHERE idx = @i)
        --PRINT 'Agency ID: ' +  CAST(@agency_id as VARCHAR(max))
        SET @agency_lob = (Select agency_lob FROM @agency_table WHERE idx = @i)
        SET @AssociatedAgencyCodes = replace((SELECT AssociatedAgencyCodes FROM @agency_table WHERE idx = @i),' ','')
        PRINT 'Associated Agency Codes: ' + CAST(@AssociatedAgencyCodes AS VARCHAR(MAX))
        --Identify record in table
        Select * FROM [AdventureWorks].[dbo].[tbl_FocusAgents] WHERE Focus_FocusAgentCd = @agency_entire_code   
        --begin updating agency record
                    BEGIN
                        UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
                        SET Focus_FarmFocus = CASE WHEN   @agency_lob = 'Farm' THEN 'Y' ELSE 'N'''  END,
                        Focus_CommercialFocus = CASE WHEN   @agency_lob = 'Commerical' THEN 'Y' ELSE 'N''' END,
                        Focus_PersonalFocus = CASE WHEN   @agency_lob = 'Personal' THEN 'Y' ELSE 'N''' END
                        WHERE Focus_FocusAgentCd= @agency_entire_code
                    eND
                    --begin updating associated agency codes
                        DECLARE @List nvarchar(100)
                        DECLARE @ListItem nvarchar(MAX)
                        DECLARE @Pos int
                        --New 'List' variable is AssociatedAgencyCodes
                        SET @List = @AssociatedAgencyCodes
                        -- Loop while the list string still holds one or more characters
                        WHILE LEN(@List) > 0
                            BEGIN
                                -- Get the position of the first comma (returns 0 if no commas left in string)
                                SET @Pos = CHARINDEX(',', @List)
                                -- Extract the list item string
                                IF @Pos = 0
                                    BEGIN
                                        SET @ListItem = @List
                                    End
                                ELSE
                                    BEGIN
                                        SET @ListItem = CONVERT(VARCHAR(MAX), '000') + SUBSTRING(@List, 1, @Pos - 1)
                                    End
                                --set associated agency codes and thier values
                                --ERROR IS OCCURING HERE IN THE UPDATE SET STATEMENTS
                            BEGIN
                                UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
                                    SET 
                                        Focus_FarmFocus = 
                                            CASE WHEN   @agency_lob = 'Farm' THEN 'Y' ELSE 'N'''  END,
                                        Focus_CommercialFocus = 
                                            CASE WHEN   @agency_lob = 'Commerical' THEN 'Y' ELSE 'N''' END,
                                        Focus_PersonalFocus = 
                                            CASE WHEN   @agency_lob = 'Personal' THEN 'Y' ELSE 'N''' END
                                    WHERE Focus_FocusAgentCd= @ListItem
                            END
                            --print for output check
                            PRINT 'SET ASSOCIATED CODE'
                            PRINT @agency_lob
                            PRINT  @ListItem
                            -- remove the list item (and trailing comma if present) from the list string
                            IF @Pos = 0
                                BEGIN
                                    SET @List = ''
                                END
                            ELSE
                                Begin
                                    -- start substring at the character after the first comma
                                    SET @List = SUBSTRING(@List, @Pos + 1, LEN(@List) - @Pos)
                                End
                        End
                    -- end consuming associated codes       
            --Increment and loop      
        SET @i = @i + 1
    END
    GO