SQLTeam.com | Weblogs | Forums

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

i'm trying to make 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

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

Since this is a stored procedure - I would simplify the process:

Declare @oldName varchar(128);

 Select @oldName = d.Name
   From Department   d
  Where d.Id = @Id;

 Insert Into EventLog(Content)
 Values(concat('User: ', @UserId, ', has updated a department name from: ', @oldName, ' to: ', @Name);