Reputation:

Converting varchar to int, SQL Server

I have code like this:

CREATE FUNCTION gantistok (@id   VARCHAR(8),
                           @id_t INT)
RETURNS INT
  BEGIN
      DECLARE @tahu  INT,
              @tempe INT,
              @hasil INT;

      SELECT @tahu = CONVERT(INT, stok)
      FROM   barang
      WHERE  id_barang = @id;

      SELECT @tempe = CONVERT(INT, jumlah)
      FROM   det_trans
      WHERE  id_det_trans = @id_t;

      SET @hasil = @tahu - @tempe;

      RETURN @hasil
  END

Why doesn't it work? I am getting this error:

Msg 402, Level 16, State 1, Procedure gantistok, Line 5
The data types text and varchar are incompatible in the equal to operator.

Upvotes: 0

Answers (3)

Christian Specht

Reputation: 36421

problem is on id_barang = @id.
id_barang contains a value that RDBMS is not able to convert to integer.

id_barang is a varchar.
So if you can't convert id_barang to integer, then you have to convert @id to varchar in the query:

  SELECT @tahu = CONVERT(INT, stok)
  FROM   barang
  WHERE  id_barang = CONVERT(varchar(50), @id);

I'm using varchar(50) because I don't know what kind of varchar exactly id_barang is.

I would suggest to always do it this way.
Even if id_barang would not contain non-integer values at the moment...it's still a text column!
Someone could insert a non-integer value anytime, so I would always make sure that my queries will work in this case as well.

Upvotes: 0