SQL, CONVERT, CAST Data Type?

Question

I am not sure how to use either CAST or CONVERT in this script.
SQL says:

Conversion failed when converting the varchar value 'Amazing' to data type int.

Can someone please tell me how to do this?

USE AdventureWorks2012  
GO

DECLARE @Number01 AS INT
DECLARE @Number02 AS INT
DECLARE @Number03 AS INT
DECLARE @Number04 AS INT
DECLARE @Number05 AS INT
DECLARE @Number06 AS INT
DECLARE @Number07 AS INT
DECLARE @Text01 AS VARCHAR (15)


SET @Number01 = 150
SET @Number02 = 200
SET @Number03 = 350
SET @Number04 = 450
SET @Number05 = 550
SET @Number06 = 650
SET @Number07 = 800
SET @Text01 = 'Amazing'


SELECT  A.ProductID  ,A.Name  ,A.ProductModelID  ,A.ProductNumber ,A.MakeFlag  ,A.Color  ,A.SafetyStockLevel
                ,A.StandardCost ,A.ListPrice,A.DaysToManufacture ,A.SellEndDate ,A.ModifiedDate ,A.ListPrice
                ,B.Name ,B.ListPrice ,B.Adjusted_List_Price ,C.ProductDescriptionID ,C.Description
                ,C.ModifiedDate ,D.ProductID ,D.StartDate ,D.EndDate ,D.ListPrice ,D.ModifiedDate, E.Name 
                ,E.CatalogDescription,E.ModifiedDate ,F.ReferenceOrderID ,F.TransactionDate
                ,F.TransactionID ,F.Quantity
                ,IIF(A.ListPrice >=@Number01,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 100 + @Number01) AS [Test90]
                ,IIF(A.ListPrice >=@Number02,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 'GG') AS [Test91]
                ,PATINDEX('%M94B%', A.ProductNumber) AS [Test92]
                ,PATINDEX('%M63S%', A.ProductNumber) AS [Test94]
                ,CASE 
                WHEN A.ProductNumber LIKE '%M94B%' THEN PATINDEX('%M94B%', A.ProductNumber) * 2
                WHEN A.ProductNumber LIKE '%M63S%' THEN  5 * @Number01
                WHEN A.ProductNumber LIKE '%T98U%' THEN @Text01
            END AS [Test95]

FROM  [Production].[Product] AS A
INNER JOIN [Production].[Product_2] AS B
ON A.Name = B.Name

INNER JOIN  [Production].[ProductDescription] AS C
ON A.ProductID = C.ProductDescriptionID

INNER JOIN  [Production].[ProductListPriceHistory] AS D
ON A.ProductID = D.ProductID

INNER JOIN   [Production].[ProductModel] AS E
ON A.ProductModelID = E.ProductModelID

FULL JOIN [Production].[TransactionHistory] AS F
ON A.ProductID = F.ProductID

WHERE A.ProductModelID IS NOT NULL
AND A.Color IS NOT NULL AND F.Quantity IS NOT NULL

score 2 · Answer 1 · answered Jun 27 '17 at 22:39

try this

USE AdventureWorks2012  
GO

DECLARE @Number01 AS INT
DECLARE @Number02 AS INT
DECLARE @Number03 AS INT
DECLARE @Number04 AS INT
DECLARE @Number05 AS INT
DECLARE @Number06 AS INT
DECLARE @Number07 AS INT
DECLARE @Text01 AS VARCHAR (15)


SET @Number01 = 150
SET @Number02 = 200
SET @Number03 = 350
SET @Number04 = 450
SET @Number05 = 550
SET @Number06 = 650
SET @Number07 = 800
SET @Text01 = 'Amazing'


SELECT  A.ProductID  ,A.Name  ,A.ProductModelID  ,A.ProductNumber ,A.MakeFlag  ,A.Color  ,A.SafetyStockLevel
                ,A.StandardCost ,A.ListPrice,A.DaysToManufacture ,A.SellEndDate ,A.ModifiedDate ,A.ListPrice
                ,B.Name ,B.ListPrice ,B.Adjusted_List_Price ,C.ProductDescriptionID ,C.Description
                ,C.ModifiedDate ,D.ProductID ,D.StartDate ,D.EndDate ,D.ListPrice ,D.ModifiedDate, E.Name 
                ,E.CatalogDescription,E.ModifiedDate ,F.ReferenceOrderID ,F.TransactionDate
                ,F.TransactionID ,F.Quantity
                ,IIF(A.ListPrice >=@Number01,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 100 + @Number01) AS [Test90]
                ,IIF(A.ListPrice >=@Number02,CAST((@Number01 *@Number02 + @Number03) AS nvarchar(50)), 'GG') AS [Test91]
                ,PATINDEX('%M94B%', A.ProductNumber) AS [Test92]
                ,PATINDEX('%M63S%', A.ProductNumber) AS [Test94]
                ,CASE 
                WHEN A.ProductNumber LIKE '%M94B%' THEN cast(PATINDEX('%M94B%', A.ProductNumber) * 2  as varchar(100))
                WHEN A.ProductNumber LIKE '%M63S%' THEN cast( 5 * @Number01 as varchar(100))
                WHEN A.ProductNumber LIKE '%T98U%' THEN @Text01
            END AS [Test95]

FROM  [Production].[Product] AS A
INNER JOIN [Production].[Product_2] AS B
ON A.Name = B.Name

INNER JOIN  [Production].[ProductDescription] AS C
ON A.ProductID = C.ProductDescriptionID

INNER JOIN  [Production].[ProductListPriceHistory] AS D
ON A.ProductID = D.ProductID

INNER JOIN   [Production].[ProductModel] AS E
ON A.ProductModelID = E.ProductModelID

FULL JOIN [Production].[TransactionHistory] AS F
ON A.ProductID = F.ProductID

WHERE A.ProductModelID IS NOT NULL
AND A.Color IS NOT NULL AND F.Quantity IS NOT NULL

score 1 · Answer 2 · answered Jun 27 '17 at 22:36

1

In your sample, the CASE statement assumes the returned data type for each WHEN clause is INT, because the first WHEN..THEN returns number.

SQL Server thinks in the 3rd WHEN..THEN, the result will be INT like the first and second.

You are breaking the assumption of SQL Server by returning a varchar data type in the 3rd WHEN..THEN and this is not right. Amazing is not convertible to INT

All paths in CASE statement should return the same data type.

answered Jun 27 '17 at 22:36

FLICKER

6,439
4
45
75

Can you please give me an example of how this change would be done in terms of Scripting please? – Shrikesh Jun 28 '17 at 15:09
The other answer should work for you. just convert the other 2 paths to return varchar instead of number – FLICKER Jun 29 '17 at 01:19

SQL, CONVERT, CAST Data Type?

2 Answers2

Linked