Conversion failed when converting the varchar value to type int

Question

2.00/5 (1 vote)

See more:

this is my storeprocedure

SQL

USE [SSDB2]
GO
/****** Object:  StoredProcedure [dbo].[SPSearching]    Script Date: 25/12/34 09:11:48 ص ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SPSearching] 
	-- Add the parameters for the stored procedure here
	@Mode varchar(50),
	@WhereClause  varchar(Max) = null
AS
if @Mode= 'ColumnNames'
BEGIN
	SELECT column_name FROM information_schema.columns WHERE table_name = 'Hadiths_old'
END

if @Mode= 'Searching'
BEGIN
Select [Hadith_Text]
      ,[Hadith_Urdu]
      ,[Hadith_English]
      ,[Chapter_English_Name]
      ,[Chapter_Urdu_Name]
      ,[Baab_English_Name]
      ,[Baab_Urdu_Name]
      ,[Baab_Id]
      ,[Hadith_Book_Number]
	  From Hadiths_old
	  Where ID < 34192 +'  '+ @WhereClause
	  order by ID

END

when i try to execute it like this

SQL

execute SPSearching @Mode ='Searching', @WhereClause = 'Hadith_Book_Number =1'

it show me this error
Msg 245, Level 16, State 1, Procedure SPSearching, Line 18
Conversion failed when converting the varchar value 'Hadith_Book_Number =1' to data type int.

Posted 29-Oct-13 19:25pm

Muhamad Faizan Khan

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sergey Alexandrovich Kryukov · Answer 1 · 2013-10-29T19:29:00

Solution 1

Here is my advice: whenever you need to store integer data, use integer database types, not varchar. In your case, the stored data is probably a string not matching integer string format. Query it as a string and see what it is under debugger.

—SA

Posted 29-Oct-13 19:29pm

Sergey Alexandrovich Kryukov

Comments

Muhamad Faizan Khan 30-Oct-13 2:12am

@WhereClause is varchar and i am passing column name with value to it

Sergey Alexandrovich Kryukov 30-Oct-13 9:12am

I see, but how about not using varchar for something intended to carry information which is int?
—SA

Madhu Nair · Answer 2 · 2013-10-29T19:31:00

Try it the below given way -

C#

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SPSearching] 
	-- Add the parameters for the stored procedure here
	@Mode varchar(50),
	@WhereClause  varchar(Max) = null
AS
if @Mode= 'ColumnNames'
BEGIN
	SELECT column_name FROM information_schema.columns WHERE table_name = 'Hadiths_old'
END
 
if @Mode= 'Searching'
BEGIN
declare @SQL VARCHAR(MAX)

SET @SQL='Select [Hadith_Text]
      ,[Hadith_Urdu]
      ,[Hadith_English]
      ,[Chapter_English_Name]
      ,[Chapter_Urdu_Name]
      ,[Baab_English_Name]
      ,[Baab_Urdu_Name]
      ,[Baab_Id]
      ,[Hadith_Book_Number]
	  From Hadiths_old
	  Where ID < 34192 '

IF @WhereClause<>''
	SET @SQL=@SQL+'  AND '+ @WhereClause + ' order by ID'

EXEC(@SQL)
 
END