Is it possible to use a SELECT statement inside a CONCAT?

December 8, 2021

I’m trying to create a string from table values, along with some text to make it more readable.

The only problem I’m having is that I can’t get the SELECT statement to work inside my CONCAT. It’s been bugging me for quite some time now, and I would appreciate any feedback on what I’m doing wrong or if there is another way to do this.

My SQL script:

CREATE PROCEDURE dbo.spDepartment_UpdateDepartment
    @UserId INT, 
    @Id INT, 
    @Name VARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EventLog(Content)
    VALUES(CONCAT('User: ', @UserId, ', has updated a department name from: ', SELECT Name FROM Department WHERE Id = @Id, ' to: ', @Name)
END

>Solution :

Just change it to an insert from a select

INSERT INTO EventLog(Content)
SELECT CONCAT('User: ', @UserId, ', has updated a department name from: ', dpmt.Name, ' to: ', @Name)
FROM Department dpmt WHERE Id = @Id;