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

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

CodePudding user response：

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;

CodePudding user response：

Usually to achieve that you can make an insert getting values from a select like this:

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