Conversion failed when converting the varchar value to data type int ошибка

Giff is almost right. The problem is that you’re trying to add a number to a string literal. Because of SQL’s type conversion rules, it tries to convert the string to a number in order to perform the addition.

You would need to convert the number to a string before concatenating it to the string literal:

SET @CUSTOMERIDS = 'AND VU_CRM_Customers.CustomerID = ' + CAST(@CustomerID As varchar(20))

But that’s not a good approach. Whilst you’re probably safe in this particular instance, since the parameters are either numbers or dates, using string concatenation to build a SQL query can leave you vulnerable to SQL Injection[^].

sp_executesql[^] gives you a much safer option: pass the parameters as parameters:

CREATE PROCEDURE SP_Customer(@FromDate date, @ToDate date, @AccessLevelCode nvarchar(30) = NULL, @UserID nvarchar(30) = NULL, @UserOrgBranchID int = NULL, @UserGroupID int = NULL, @CustomerID int = 0)
AS
BEGIN
DECLARE @SQL nvarchar(max), @params nvarchar(max);
DECLARE @AccessFilter nvarchar(max), @CustomerIDs nvarchar(max);

    SET NOCOUNT ON;
    SET @AccessFilter = dbo.fnGetAccessFilter(@AccessLevelCode, @UserID, @UserOrgBranchID, @UserGroupID);
    
    SET @CustomerIDs = CASE
        WHEN @CustomerID Is Null THEN N''
        ELSE N'AND VU_CRM_Customers.CustomerID = @CustomerID'
    END;

    SET @SQL = N'	
    DECLARE @Temp TABLE
    (
        [Row] INT,
        Customer NVARCHAR(MAX),
        CustomerID int,
        TransactionType NVARCHAR(MAX),
        VoucherDate smalldatetime,
        VoucherNo NVARCHAR(MAX),
        AccountingDate datetime,
        Amount numeric(18,3),
        Debit numeric(18,3),
        Credit numeric(18,3),        
        [OrgID] INT
    );
    
    INSERT INTO @Temp 
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY CreatedDate) AS Row, 
        * 
    FROM
        (
            SELECT 
                CASE 
                    WHEN ISNULL(ISNULL(Mobile,Phone),'''') = '''' THEN Customer 
                    ELSE CONCAT(Customer,''('',ISNULL(Mobile,Phone),'')'') 
                END AS Customer,
                ''Opening Balance'' AS TransactionType,
                AccountingDate AS VoucherDate,
                COALESCE(ACC_FinOpeningBalance.ReferenceNo, CONVERT(nvarchar,OpeningBalanceID)) AS VoucherNo,
                AccountingDate AS CreatedDate,
                CASE WHEN Dr > 0 THEN Dr ELSE Cr END AS Amount,
                Dr AS Debit,
                Cr AS Credit,
                ACC_FinOpeningBalance.OrgID
            FROM 
                ACC_FinOpeningBalance 
                INNER JOIN VU_CRM_Customers 
                ON ACC_FinOpeningBalance.SLGroupCode = ''C'' 
                AND ACC_FinOpeningBalance.SubledgerID = VU_CRM_Customers.CustomerID
            WHERE 
                ACC_FinOpeningBalance.CreatedBy' + @AccessFilter + @CustomerIDs + N'
            
            UNION
            
            SELECT 
                 CASE 
                     WHEN ISNULL(ISNULL(Mobile,Phone),'''')='''' THEN Customer 
                     ELSE CONCAT(Customer,''('',ISNULL(Mobile,Phone),'')'') 
                 END AS Customer,
                ''Advance'' AS TransactionType,
                 AdvanceDate AS VoucherDate,
                 COALESCE(ACC_AdvanceReceived.ReferenceNo, CONVERT(nvarchar,AdvanceReceivedID)) AS VoucherNo,
                 AdvanceDate AS CreatedDate,
                 NetReceivable AS Amount,
                 NetReceivable AS Debit,
                 0 AS Credit,
                 ACC_AdvanceReceived.OrgID 
            FROM 
                ACC_AdvanceReceived 
                INNER JOIN VU_CRM_Customers 
                ON ACC_AdvanceReceived.AccountID = VU_CRM_Customers.CustomerID
            WHERE 
                Customer IS NOT NULL
            AND 
                ACC_AdvanceReceived.CreatedBy' + @AccessFilter + @CustomerIDs + N'
        )
    ;
    
    SELECT 
        *,
        (M.OpeningBalance + M.Debit - M.Credit) AS ClosingBalance 
    FROM
        (
            SELECT 
                L.Row,
                L.Customer,
                L.TransactionType,
                L.VoucherDate,
                L.VoucherNo,
                L.AccountingDate,
                L.Amount,
                L.OrgID,
                ISNULL((SELECT SUM(Debit) - SUM(Credit) FROM @Temp AS O WHERE O.AccountingDate <= L.AccountingDate AND O.Customer = L.Customer AND O.Row < L.Row), 0) AS OpeningBalance,
                L.Debit,
                L.Credit 
            FROM 
                @Temp AS L 
            WHERE 
                L.AccountingDate  >= @FromDate 
            And 
                L.AccountingDate < @ToDate
        ) As M
    ;';
    
    SET @params = N'@FromDate date, @ToDate date, @CustomerID int';
    EXEC sp_executesql @SQL, @params, @FromDate = @FromDate, @ToDate = @ToDate, @CustomerID = @CustomerID;
    print @SQL;
END

NB: You can probably do something about the value returned from fnGetAccessFilter as well; but since I can’t see the code for that function, I can’t tell you.

NB2: Tidying up your dynamic query makes it obvious that you have several misplaced brackets. Once you’d fixed the conversion error, the next error would be a syntax error trying to execute your query. I think I’ve fixed it, but you should probably check.

NB3: Depending on which version of SQL Server you’re running, there is probably a much cleaner way to generate the running totals. If it’s 2012 or later, you can use the LAG function[^].

The Reason for this error is you are converting a varchar value to Int or Numeric or any Other datatype let us take a simple example 

select CAST(‘abc’ as int)

what we are doing in above query is changing the nvarchar value to int which is not possible that why it throw the error.

While working with TSQL Coding if you are adding some string value with alphanumeric field  you could face the above problem if you have not written your query properly.

Take an Example where you want to show empidEmpsalary from employee table

for this we will create a table below