Concatenating sql table rows and converting a data type

Jes Sie

I'm concatenating multiple table rows into one row. Below code is the working only with nvarchar data type but not with int.

ALTER PROCEDURE [dbo].[spViewConcatenatedPolicyCInoForTaxInvoice_motorOnly]
-- Add the parameters for the stored procedure here
@TaxInvoiceNumber nvarchar(50)
AS
BEGIN
DECLARE @MotorCINo int
SET @MotorCINo=''
SELECT @MotorCINo = @MotorCINo + MotorCINo +', '
FROM CashierSalesDetails
WHERE TaxInvoiceNumber = @TaxInvoiceNumber
SELECT LEFT(@MotorCINo,LEN(@MotorCINo)-1) AS PolicyCINo
END

below is the error:

Msg 245, Level 16, State 1, Procedure spViewConcatenatedPolicyCInoForTaxInvoice_motorOnly, Line 17
Conversion failed when converting the varchar value ', ' to data type int.

Any is greatly appreciated.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
Accepted answer
hi try this
you have to declare parameter @MotorCINo as nvarchar not int in order to concanate strings.. as follows you can try
DECLARE @MotorCINo nvarchar(Max)
      SET @MotorCINo=''

      SELECT @MotorCINo = @MotorCINo  + cast(MotorCINo as varchar) +', '
      FROM    CashierSalesDetails
      WHERE TaxInvoiceNumber = @TaxInvoiceNumber
      SELECT  LEFT(@MotorCINo,LEN(@MotorCINo)-1) AS PolicyCINo
Ramesh Palanivel9y ago
Hi,

Use the cast wherever you concatenating the varchar with int ...

Search in internet , you can get clear idea about it.
Jes Sie9y ago
@Ramesh Palanivel thanks for the quick reply. Would you mind showing me where to place the cast in the code that I provided above? I tried to place it in the following order by got the same error:
attempt 1:
DECLARE @MotorCINo int
        SET @MotorCINo=''

        SELECT @MotorCINo = cast(@MotorCINo as varchar) + MotorCINo +', '
        FROM    CashierSalesDetails
        WHERE TaxInvoiceNumber = @TaxInvoiceNumber
        SELECT  LEFT(@MotorCINo,LEN(@MotorCINo)-1) AS PolicyCINo

attempt 2:

    DECLARE @MotorCINo int
        SET @MotorCINo=''

        SELECT @MotorCINo = @MotorCINo  + cast(MotorCINo as varchar) +', '
        FROM    CashierSalesDetails
        WHERE TaxInvoiceNumber = @TaxInvoiceNumber
        SELECT  LEFT(@MotorCINo,LEN(@MotorCINo)-1) AS PolicyCINo
I'm new in sql server. Thanks
Ramesh Palanivel9y ago
HI,

You can only able to concatenate the varchar values, You cannot convert the integer to varchar. If you want to concatenate the interger with varchar , convert the integer value into varchar while concatenating.

Try this , IT may help you
cast(intName as varchar) // intname is your integer column, convert it to varchar for concatination