How to concatenate 2 numbers using dot using T-SQL?

Question

I have a EmployeeAudit table like below :

Id      EmployeeId      
1       100
2       101

I have a 2 below variable:

DECLARE @Version INT = 10;
DECLARE @isAuditDone BIT = 0;
//logic to populate Version and isAuditDone

Based on above variable, I want to check if Audit is done then I want to return Id from EmployeeAudit like below :

if @isAuditDone
    return Id + '.' + Version = 1.10
else 
    return Id

Query:

select 
    (CASE
          WHEN @isAuditDone = 1
                THEN E.Id + '.' + @Version
          WHEN @isAuditDone = 0
                THEN E.Id
          ELSE 
                E.Id
          END) AS Id, 
          EmployeeId
    from 
    EmployeeAudit E

But I am getting below error:

Conversion failed when converting the varchar value '.' to data type int.

Can someone please help?

Update :

EXEC [dbo].[GetEmployeeAuditJson] @employeeId = 101

SP return type on select statement:

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;

Answer 1

If you want to return something other than integer value from Stored Procedure then you need to use Output column. So in your case you have to return String as you have a Dot to append with number. This is a demo stored procedure definition you can try using to fulfil what you desire.

CREATE PROCEDURE  SProc
@Id varchar(500) OUTPUT
AS 
select 
    (CASE
          WHEN @isAuditDone = 1
                THEN concat(E.Id,'.',@Version)
          WHEN @isAuditDone = 0
                THEN concat('',E.Id)   
          ELSE 
                concat('',E.Id)       
          END) AS Id, 
          EmployeeId
    from 
    EmployeeAudit E

You can then execute your stored proc like this with output parameter.

Declare @Id varchar(500)
EXEC SProc  @Id  OUTPUT