Error converting data type varchar to int dynamic SQL select statement

Question

I am trying to execute sp_executesql inside a stored procedure. When I execute the stored procedure, it throws this following error

Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to int

I couldn't figure it out where the error occurs

Here is my stored procedure

ALTER PROCEDURE [dbo].[spHoldTransaction] 
    @RegisterNo INT,
    @StoreID INT,
    @Department VARCHAR(50),
    @TransactionDateFrom DATE,
    @TransactionDateTo DATE,
    @Comment VARCHAR(50)
AS
BEGIN
    DECLARE @DatabaseName VARCHAR(15);

    IF (@StoreID = 5555)
    BEGIN
        SET @DatabaseName = 'xxx'
    END

    SET @Department=''''+REPLACE(@Department,',',''',''')+''''

    DECLARE @HoldEntryQry NVARCHAR(MAX);
    DECLARE @HoldEntryParamDefinition NVARCHAR(MAX);

    SET @HoldEntryParamDefinition = N'@Department VARCHAR(50),@StoreID INT,@NewId INT,@TransactionDateFrom DATETIME,@TransactionDateTo DATETIME'    

    DECLARE @NewId INT;
    SET @NewID = 15;

    SET @HoldEntryQry = 'SELECT te.StoreID, ' + CONVERT(VARCHAR(MAX), @NewId) + ' AS TransactionHoldID,
                             te.ItemLookupCode,
                             item.Description,
                             te.ItemID,
                             SUM(te.Quantity) Quantity,
                             SUM(te.SalesTax) Tax,
                             SUM(te.Price * te.Quantity) Value,
                             SUM(te.Price * te.Quantity) / SUM(te.Quantity) Price,  
                             item.price FullPrice,
                             --1 AS Taxable,
                             GETDATE() AS TransactionTime,
                             ''1899-12-30'' AS VoucherExpirationDate
                         FROM
                             TransactionEntry te
                         LEFT JOIN
                             item ON item.id = te.ItemID
                         INNER JOIN
                             item nItem ON nitem.ItemLookupCode = item.itemlookupcode
                         WHERE
                             te.TransactionDate >= ''' + CONVERT(VARCHAR, CAST(@TransactionDateFrom AS DATE), 102) + 
                         ''' AND te.TransactionDate <= ''' +
                             CONVERT(VARCHAR, CAST(@TransactionDateTo AS DATE), 102) +
                         ''' AND item.department IN (' + @Department + ') 
                             AND te.storeid = ' + convert(varchar(max), @StoreID)+'
    group by
        te.StoreID,
        te.ItemLookupCode,
        item.Description,
        te.ItemID,
        item.price
   '
EXECUTE sp_executesql @HoldEntryQry, @HoldEntryParamDefinition, @NewId, @Department, @Storeid,@TransactionDateFrom, @TransactionDateTo 
END

When I do Print @HoldEntryQry, the query looking like below

 select 
    te.StoreID,
    15 AS TransactionHoldID,
    te.ItemLookupCode,
    item.Description,
    te.ItemID,
    sum(te.Quantity) Quantity,
    sum(te.SalesTax) Tax,
    sum(te.Price * te.Quantity) Value,
    sum(te.Price * te.Quantity)/sum(te.Quantity) Price,  
    item.price FullPrice,
    1 AS Taxable,
    Getdate() AS TransactionTime,
    '1899-12-30' AS VoucherExpirationDate

from
    TransactionEntry te
    left join item on item.id = te.ItemID
    inner join item nItem on nitem.ItemLookupCode = item.itemlookupcode
where
    te.TransactionDate >= '2018.04.16' and te.TransactionDate <='2018.04.30' and item.department in ('Apple','Orange','Dates') and te.storeid = 5555
group by
    te.StoreID,
    te.ItemLookupCode,
    item.Description,
    te.ItemID,
    item.price

The printed query is returning the exact result I expect

For starters you should ALWAYS specify the scale of varchar. https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length Also you should use ANSI compliant date format of YYYYMMDD. Those periods in there will cause issues in some situations. — Sean Lange, May 17 '18 at 13:49
But the biggest question for me is why are you using dynamic sql here? From what you posted it is just a layer of complexity that is not needed. — Sean Lange, May 17 '18 at 13:52
@SeanLange are you talking about `voucherExpirationDate`. Please leave that one. And thanks for your concern — mohamed faisal, May 17 '18 at 13:52
That is just a string that looks like a date. I was looking at your where clause. — Sean Lange, May 17 '18 at 13:53
@SeanLange I really needed. My case is entirely different. DatabaseName will not be same all the time — mohamed faisal, May 17 '18 at 13:53
@SeanLange Even I remove complete `Where clause`, still it throws the same error. I am confirmed once again just now — mohamed faisal, May 17 '18 at 13:55
So this isn't really your code then? You are adding the database name in your dynamic sql in the actual code. Without a bit more detail I don't see anything that is obviously the issue. My guess is that you have some datatypes mixed up in your tables. — Sean Lange, May 17 '18 at 13:56
@SeanLange This error is coming from the same query What I mentioned here. — mohamed faisal, May 17 '18 at 13:57
Do you really mean to join item to itself on the same column? I would think maybe nitem.id? — Sean Lange, May 17 '18 at 13:57
Of course it comes from your query. But I can't see your tables or run anything. Can you share the ddl for those tables? — Sean Lange, May 17 '18 at 13:59

score 0 · Answer 1 · answered May 17 '18 at 13:59

The problem is probably +@Department+. If any argument to + is not a string, you are probably going to get a conversion error.

But don't just do cast(@department as varchar(255)). Fix the root cause of the problem. You already know about parameters. Use them for all the constants in the where clause! This will make your debugging easier.

Error converting data type varchar to int dynamic SQL select statement

1 Answers1