Click here to Skip to main content
15,794,629 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello friends,

I'm trying to create dynamic SQL statement, but I'm getting following error

Conversion failed when converting the varchar value 'Dog' to data type int
The error is on the line 40

What does this error actually means and where is this error in my StoredProcedure
USE exercise
GO
CREATE PROC REPORT
@customerID VARCHAR
AS
SELECT * INTO #temp FROM customer,animal WHERE customer.customerID = @customerID
ALTER TABLE #temp
ADD
Printed SMALLINT
UPDATE #temp
SET Printed = 0
DECLARE @customerName VARCHAR (30)
DECLARE @customerTelephone VARCHAR(30)
DECLARE @animalID INT
DECLARE @animalName VARCHAR (30)
DECLARE @quantity INT
DECLARE @price INT
DECLARE @speciesName VARCHAR(40)
DECLARE @totalPrice INT
SELECT	
		@customerName = customer.customerName,
		@customerTelephone = customer.customerTelephone
		FROM customer 
		WHERE @customerID = customer.customerID

		PRINT 'CustomerID: ' +@customerID
		PRINT 'Customer Name ' +@customerName
		PRINT 'Customer Telephone: ' +@customerTelephone
		PRINT'animalID  animalName  quantity  price  Species  totalPrice'
		WHILE EXISTS (SELECT * FROM #temp WHERE Printed = 0)
		BEGIN
		SELECT @animalID = MIN(animalID) FROM #temp WHERE Printed = 0
		
		SELECT @animalName = animalName,
				@quantity = animalCustomer.quantity,
				@speciesName = species.specieName
				FROM animal
				 INNER JOIN animalCustomer ON animal.animalID = animalCustomer.animalIdentificater 
				INNER JOIN species ON species.specieName = animal.speciesIdentificater
				WHERE animal.animalID = @animalID
				SET @totalPrice = @price * @quantity			
				PRINT @animalID+'  '+@animalName+'  '+@quantity+'  '+@speciesName+'  '+@totalPrice			
		UPDATE #temp
				SET Printed = 1
				WHERE @animalID = animalID
		END
		
		
DROP TABLE #temp
GO
Posted
Updated 27-Mar-13 12:19pm
v3
Comments
Richard C Bishop 27-Mar-13 17:19pm    
The error means you can not convert a varchar into an int. I think it might be taking place when you try and inner join your tables. "animal.animalID" is probably a number while "animalCustomer.animalIdentificater" is a varchar. What values are in the animal.animalID column and the other? Also, Identificater is not a word unless you are just using it for fun.
lui1356 27-Mar-13 17:24pm    
numbers (1,3,4)
Richard C Bishop 27-Mar-13 17:29pm    
Both columns are numbers?
Maciej Los 27-Mar-13 18:00pm    
Identificater - it is a word, probably with misspelling error. BTW, "Identyfikator" in Poland means: identification number (ID).
Richard C Bishop 28-Mar-13 9:59am    
Well, it is not an English word and since the OP claims to be from the US I figured it was worth mentioning. Identificator is Spanish and means the same thing basically.

1 solution

PRINT 'CustomerID: ' + cast(@customerID as varchar(255))
And all int Variables when printing
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900