Dynamic query problem

Question

I have a problem with a dynamic query as shown below:

Declare @SQL1 varchar(max)
Set @SQL1 = '
Insert into #TEMPSelect 
SELECT DISTINCT tbAgent.Agent,
    ...
    ...,
    case when coalesce(tbsystemuser.MiddleName, '''') = '''' then  tbsystemuser.FirstName + '' '' + tbsystemuser.LastName 
    else tbsystemuser.FirstName + '' '' + tbsysten.MiddleName + '' '' +  tbsystemuser.LastName end,
    tbsystemuser.EmailAddress,
    tbagent.AgentCode,
    tbBroker.businessname 
FROM tbSystemUser 
inner JOIN dbo.tbAgent 
    ON tbSystemUser.SystemUser=tbAgent.SystemUser
WHERE (tbagent.DeletionIndicator=0 and tbsystemuser.SystemRoleType!=10 ) '

If LEN (@Name) >1
Set @SQL1 = @SQL1 + 'and FullName LIKE ''' + @Name + ''''
Exec(@SQL1)

He does not return anything! I think the problem is with the line coalesceand '' ''.

Answer 1

Try it,

DECLARE @SQL1 VARCHAR(max) = ''
    ,@Name VARCHAR(100) = 'test'

SET @SQL1 = 'INSERT INTO #TEMPSelect
SELECT DISTINCT tbAgent.Agent
    ,CASE 
        WHEN coalesce(tbsystemuser.MiddleName, '''') = ''''
            THEN tbsystemuser.FirstName + '' '' + tbsystemuser.LastName
        ELSE tbsystemuser.FirstName + '' '' + tbsysten.MiddleName + '' '' + tbsystemuser.LastName
        END AS FULLNAME
    ,tbsystemuser.EmailAddress
    ,tbagent.AgentCode
    ,tbBroker.businessname
FROM tbSystemUser
INNER JOIN dbo.tbAgent ON tbSystemUser.SystemUser = tbAgent.SystemUser
WHERE (
        tbagent.DeletionIndicator = 0
        AND tbsystemuser.SystemRoleType != 10
        )'

IF LEN(@Name) > 1
    SET @SQL1 = @SQL1 + 'and FullName LIKE ''%' + @Name + '%'''

EXEC (@SQL1)

As Arulkumar said, you must specify an alias for the case argument. In addition to this, the use of% in a similar statement