Clarification - Using Update TSQL

Question

I have a stored procedure that looks like the following, below. What happens is that it fails on me when I run. I have narrowed it down where when I am attempting to update the the AGENT STATE syntax in e.g. WHEN PropertyDefinitionID = @passStatePropertyDefID THEN @passState -- Agent State

ALTER procedure [dbo].[usp_UpdateProfile] 
    @passCompanyName varchar(100),
    @passFName varchar(50),
    @passLName varchar(50),
    @passEmail varchar(50),
    @passStreet varchar(50),
    @passCity varchar(50),
    @passState int,
    @passZip varchar(10),
    @passPhone varchar(15),
    @passUserID int
As
Begin
    Declare @passCompanyNamePropertyDefID int 
    Declare @passFNamePropertyDefID int 
    Declare @passLNamePropertyDefID int 
    Declare @passEmailPropertyDefID int
    Declare @passStreetPropertyDefID int
    Declare @passCityPropertyDefID int
    Declare @passStatePropertyDefID int
    Declare @passPostalPropertyDefID int
    Declare @passPhonePropertyDefID int
    Declare @passACURefID int

    Set @passCompanyNamePropertyDefID = 62 -- Local PropertyDefinitionID from server : Agent Company Name
    Set @passFNamePropertyDefID = 61 -- Local PropertyDefinitionID from server : Agent First Name
    Set @passLNamePropertyDefID = 63 -- Local PropertyDefinitionID from server : Agent Last Name
    Set @passEmailPropertyDefID = 64 -- Local PropertyDefinitionID from server : Agent Email    
    Set @passStreetPropertyDefID = 65 -- Local PropertyDefinitionID from server : Agent Street
    Set @passCityPropertyDefID = 66 -- Local PropertyDefinitionID from server : Agent City
    Set @passStatePropertyDefID = 72 -- Local PropertyDefinitionID from server : Agent State
    Set @passPostalPropertyDefID = 67 -- Local PropertyDefinitionID from server : Agent State
    Set @passPhonePropertyDefID = 68 -- Local PropertyDefinitionID from server: Agent Telephone

    If(Exists(Select UserID From AgentCompanyUser Where UserID = @passUserID))
    Begin
        -- Modify First Name and Last Name in AgentCompanyUser table --
        Update AgentCompanyUser Set Agent_FirstName = @passFName, Agent_LastName = @passLName Where UserID = @passUserID        
    End

    -- Modify Email Address in dnn_Users table --
    Update dnn_Users Set Email = @passEmail Where UserID = @passUserID

    -- Retreive ACU_RefID from AgentCompanyUser table --
    Set @passACURefID = (Select ACU_RefID from AgentCompanyUser Where UserID = @passUserID) 

    -- UPDATE COMPANY WITH AGENT - AGENT PROFILE SECTION
    Update dnn_UserProfile  
    Set PropertyValue = CASE WHEN PropertyDefinitionID = @passFNamePropertyDefID THEN @passFName -- Agent First Name
                            WHEN PropertyDefinitionID = @passLNamePropertyDefID THEN @passLName -- Agent Last Name
                            WHEN PropertyDefinitionID = @passEmailPropertyDefID THEN @passEmail -- Agent Email
                            WHEN PropertyDefinitionID = @passStreetPropertyDefID THEN @passStreet -- Agent Street
                            WHEN PropertyDefinitionID = @passCityPropertyDefID THEN @passCity -- Agent City
                            WHEN PropertyDefinitionID = @passStatePropertyDefID THEN @passState -- Agent State
                            WHEN PropertyDefinitionID = @passPostalPropertyDefID THEN @passZip -- Agent Postal
                            WHEN PropertyDefinitionID = @passPhonePropertyDefID THEN @passPhone -- Agent Phone
                        END
    WHERE PropertyDefinitionID IN (@passFNamePropertyDefID, @passLNamePropertyDefID, @passEmailPropertyDefID, @passStreetPropertyDefID, @passCityPropertyDefID, @passStatePropertyDefID, @passPostalPropertyDefID, @passPhonePropertyDefID) 
        AND UserID IN (
        SELECT B.UserID 
        FROM CompanyAuthorizeAgent AS B
            INNER JOIN AgentCompanyUser AS A ON A.ACU_RefID = B.FK_acu_RefID 
        WHERE A.ACU_RefID = @passACURefID )
End

But if modify the code to separate the code which is causing me problems. It works. The modify code below, you'll see I created another update:

ALTER procedure [dbo].[usp_UpdateProfile] 
    @passCompanyName varchar(100),
    @passFName varchar(50),
    @passLName varchar(50),
    @passEmail varchar(50),
    @passStreet varchar(50),
    @passCity varchar(50),
    @passState int,
    @passZip varchar(10),
    @passPhone varchar(15),
    @passUserID int
As
Begin
    Declare @passCompanyNamePropertyDefID int 
    Declare @passFNamePropertyDefID int 
    Declare @passLNamePropertyDefID int 
    Declare @passEmailPropertyDefID int
    Declare @passStreetPropertyDefID int
    Declare @passCityPropertyDefID int
    Declare @passStatePropertyDefID int
    Declare @passPostalPropertyDefID int
    Declare @passPhonePropertyDefID int
    Declare @passACURefID int

    Set @passCompanyNamePropertyDefID = 62 -- Local PropertyDefinitionID from server : Agent Company Name
    Set @passFNamePropertyDefID = 61 -- Local PropertyDefinitionID from server : Agent First Name
    Set @passLNamePropertyDefID = 63 -- Local PropertyDefinitionID from server : Agent Last Name
    Set @passEmailPropertyDefID = 64 -- Local PropertyDefinitionID from server : Agent Email    
    Set @passStreetPropertyDefID = 65 -- Local PropertyDefinitionID from server : Agent Street
    Set @passCityPropertyDefID = 66 -- Local PropertyDefinitionID from server : Agent City
    Set @passStatePropertyDefID = 72 -- Local PropertyDefinitionID from server : Agent State
    Set @passPostalPropertyDefID = 67 -- Local PropertyDefinitionID from server : Agent State
    Set @passPhonePropertyDefID = 68 -- Local PropertyDefinitionID from server: Agent Telephone

    If(Exists(Select UserID From AgentCompanyUser Where UserID = @passUserID))
    Begin
        -- Modify First Name and Last Name in AgentCompanyUser table --
        Update AgentCompanyUser Set Agent_FirstName = @passFName, Agent_LastName = @passLName Where UserID = @passUserID        
    End

    -- Modify Email Address in dnn_Users table --
    Update dnn_Users Set Email = @passEmail Where UserID = @passUserID

    -- Retreive ACU_RefID from AgentCompanyUser table --
    Set @passACURefID = (Select ACU_RefID from AgentCompanyUser Where UserID = @passUserID) 

    -- UPDATE COMPANY WITH AGENT - AGENT PROFILE SECTION
    Update dnn_UserProfile  
    Set PropertyValue = CASE WHEN PropertyDefinitionID = @passFNamePropertyDefID THEN @passFName -- Agent First Name
                            WHEN PropertyDefinitionID = @passLNamePropertyDefID THEN @passLName -- Agent Last Name
                            WHEN PropertyDefinitionID = @passEmailPropertyDefID THEN @passEmail -- Agent Email
                            WHEN PropertyDefinitionID = @passStreetPropertyDefID THEN @passStreet -- Agent Street
                            WHEN PropertyDefinitionID = @passCityPropertyDefID THEN @passCity -- Agent City
                            WHEN PropertyDefinitionID = @passPostalPropertyDefID THEN @passZip -- Agent Postal
                            WHEN PropertyDefinitionID = @passPhonePropertyDefID THEN @passPhone -- Agent Phone
                        END
    WHERE PropertyDefinitionID IN (@passFNamePropertyDefID, @passLNamePropertyDefID, @passEmailPropertyDefID, @passStreetPropertyDefID, @passCityPropertyDefID, @passPostalPropertyDefID, @passPhonePropertyDefID)  
        AND UserID IN (
        SELECT B.UserID 
        FROM CompanyAuthorizeAgent AS B
            INNER JOIN AgentCompanyUser AS A ON A.ACU_RefID = B.FK_acu_RefID 
        WHERE A.ACU_RefID = @passACURefID )

    -- UPDATE COMPANY WITH AGENT - AGENT PROFILE SECTION (Add 2nd Update)
    Update dnn_UserProfile  
    Set PropertyValue = CASE WHEN PropertyDefinitionID = @passStatePropertyDefID THEN @passState-- Agent State
                        END
    WHERE PropertyDefinitionID IN (@passStatePropertyDefID) 
        AND UserID IN (
        SELECT B.UserID 
        FROM CompanyAuthorizeAgent AS B
            INNER JOIN AgentCompanyUser AS A ON A.ACU_RefID = B.FK_acu_RefID 
        WHERE A.ACU_RefID = @passACURefID )

End

My question is why do I have to create another update instead of using my original code to make this work?

Thanks in advance.

Kash · Accepted Answer

This is a classic case of data type precedence in SQL Server.

Int always has higher precedence than varchar.

Your First Block

In the update statement of the first block, when even one of the result expressions of the CASE statement has an int datatype (in this case @passState) and the rest of the result expressions of the CASE statement evaluate to varchar datatype, then the result expression of all are expected to be Int because Int has higher precedence over varchar.

But as the rest of the result expressions of the CASE evaluates to varchar, you will get a syntax error on the first CASE result expression @passFName as it is expecting an int for all:

Syntax error converting the varchar value ** to a column of data type int.

Your Second Block

In the second block, the first update statement has the CASE where all result_expressions evaluate to varchar. Hence it expects varchar and there are no conversion issues.

In the second update statement, there is only one result expression that evaluates to an int (@passState). Hence it implicitly converts int to varchar.

Solution

The solution of course is to convert @passState to varchar in the CASE statement in the first block either using CASE or CONVERT as shown below.

// ......................
CASE WHEN (PropertyDefinitionID = @passFNamePropertyDefID) THEN @passFName -- Agent First Name
    WHEN PropertyDefinitionID = @passLNamePropertyDefID THEN @passLName -- Agent Last Name
    WHEN PropertyDefinitionID = @passEmailPropertyDefID THEN @passEmail -- Agent Email
    WHEN PropertyDefinitionID = @passStreetPropertyDefID THEN @passStreet -- Agent Street
    WHEN PropertyDefinitionID = @passCityPropertyDefID THEN @passCity -- Agent City
    WHEN PropertyDefinitionID = @passStatePropertyDefID THEN CONVERT(varchar(50), @passState) -- Agent State
    --WHEN PropertyDefinitionID = @passStatePropertyDefID THEN CAST(@passState as varchar(50)) -- Agent State
    WHEN PropertyDefinitionID = @passPostalPropertyDefID THEN @passZip -- Agent Postal
    WHEN PropertyDefinitionID = @passPhonePropertyDefID THEN @passPhone -- Agent Phone
END
// ........................

References:

Data Type Precedence

Quote from CAST statement: *Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence (Transact-SQL).*

Clarification - Using Update TSQL

Answers (1)

Related Questions