conversion failed when converting the nvarchar value '%' to data type int

I've created a SP with select statement for use with crystal report. There is no cast/convert statement in the SP. This error occurred after i added in new columns into the view. Please help. Thanks

Here's the SP syntax :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CST_Std Material Cost]
 
	@BP varchar(10),
	@ProcessVal char(1)
 
	
AS
 
If @ProcessVal = '1' /*With landed cost*/
BEGIN
 
	select * from  reporting.dbo.[Item Master With Cost] left join  reporting.dbo.[Supplier Price Catalogue]
	on ibitm = cbitm and ibmcu = cbmcu
	where ltrim(ibmcu) = rtrim(@BP) and a6prp5 <> ''
 
 
END
 
If @ProcessVal = '2' /*Without landed cost*/
BEGIN
 
	select * from  reporting.dbo.[Item Master With Cost] left join  reporting.dbo.[Supplier Price Catalogue]
	on ibitm = cbitm and ibmcu = cbmcu
	where ltrim(ibmcu) = rtrim(@BP)  and a6prp5= ''
 
END
 
If @ProcessVal = '3' /*All*/
BEGIN
 
	 	select * from  reporting.dbo.[Item Master With Cost] left join  reporting.dbo.[Supplier Price Catalogue]
	on ibitm = cbitm and ibmcu = cbmcu
	where ltrim(ibmcu) = rtrim(@BP)
 
END

Open in new window

sacred21Asked:
Who is Participating?
 
sacred21Author Commented:
Anyway, i managed to solve the issue by removing the parameters and put them back into the SP and verfiy the crystal report. It works fine now.

Thanks all for your effort & attention.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
what are the datatypes of  ibitm , cbitm and ibmcu , cbmcu
0
 
Olaf DoschkeSoftware DeveloperCommented:
So what changed? New fields in dbo.report? What new fields? Did you change the stored procedure? You SELECT * FROM dbo.reports, so you select new fields. If The crystal report has no use for the new fields you're perhaps getting the error therefore.

What error do you get? Do you get it from SQL Server or from crystal report?

Bye, Olaf.
0
 
sacred21Author Commented:
ibitm & cbitm are float

ibmcu & cbmcu are nchar
0
 
sacred21Author Commented:
To Olaf,

The error was from MS-SQL. The complete error message is
"Database connector error 22018 : [Microsoft][SQL Native Client][SQL Server] conversion failed when converting the nvarchar value '%' to data type int"

The new fields are being added into the views ([Item Master With Cost] & [Supplier Price Catalogue] are views)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.