Converting DataType's.

Question

Hi All

I have an DML insert Trigger and I'm inserting from one database to another.

I have set and declared the following

DECLARE @newANALCODE varchar(8)
SET @newANALCODE = (SELECT [ANAL CODE] FROM Inserted)

When I go to Insert into the table I'm hit with the following error

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

Part of my Insert Trigger is as follow's

SELECT @productId = SCOPE_IDENTITY()
 
INSERT INTO [ConcordWholesales_new].[dbo].[Product_Category_Mapping]
           ([ProductId]
           ,[CategoryId]
           ,[IsFeaturedProduct]
           ,[DisplayOrder]
           ,[CCCAnalCode]
           ,[CCCSKU])
VALUES
           (@productId,
            isnull(@newANALCODE,'0++')
           ,'False'
           ,'1'
           ,@newANALCODE
           ,@newSTOCKCODE)

AnalCode is the CategoryId and links all products under the speafic header.

My question.

How do I code the conversion from Varchar to Int?

Thank you in advance

Ieaun.

Answer 1

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

By that error message you are trying toc convert the text "BB01" into an numeric/integere value; of course that's not possible.

---

Olaf Helper

Blog Xing

Answer 2

Right Thanks.

Answer 3

If @CategoryId is an integer column, then your error is explained.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

After three decades of fixing bad SQL, one of the first things I look for in code is leading commas with one data element per line. This is how we used punch cards fifty years ago so we could re-use and re-arrange the deck. It is like a high fever to a doctor; a symptom but not a certainty. 

After three decades of fixing bad SQL, one of the first things I look for in code is leading commas with one data element per line. This is how we used punch cards fifty years ago so we could re-use and re-arrange the deck. It is like a high fever to a doctor; a symptom but not a certainty. It lets me do triage. 

Would you mind posting DDL, so we can see the keys, constraints, data types, etc. that are absolutely required to code by people who cannot read minds? From the little you showed us, this looks like  bad schema that is running 2-3 orders of magnitude worse that it should.  

You do know that we RDBMS/SQL programmers  hate triggers and prefer DRI actions? RDBMS  do not use IDENTITY and other physical storage properties; that was magnetic tape files. We do not use bit flags; that was assembly language.  We love industry standards, like UPC, EAN, GTIN, etc to identify products.  We also follow ISO-11179 rules about data element names; that we you can have a "<something>_category" or a  "<something>_id" in a correctly designed schema. The post fixes "category" and "id" are called attribute properties and have to have an attribute to modify. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL