Error while excuting the stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I gave a stored procedure like this:

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[RecordManagement_getCollgeLocation]
(
   @Category nvarchar(100)
)
AS
Select Location_Name
From Location
where Loc_ID IN(Select College_ID
From Records
WHERE (DocumentCategory LIKE '%'+@Category+'%' OR Document_Name LIKE '%'+@Category+'%' OR Document_Number LIKE '%'+@Category+'%'))

Upon hitting the execute button it get..

HTML

Command(s) completed successfully.

Till here its all OK.but when i add in the values @category values from the Excute Stored Procedure i get an error

HTML

Msg 245, Level 16, State 1, Procedure RecordManagement_getCollgeLocation, Line 7
Conversion failed when converting the nvarchar value 'SM                  ' to data type int.

Any way to solve this

Posted 2-Sep-12 17:20pm

Member 9291223

Updated 2-Sep-12 17:22pm

Kenneth Haugland

v2

Add a Solution

4 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)

Tim Corey · Answer 1 · 2012-09-02T17:37:00

Your error message is telling you what the problem is. In your WHERE statement, you have Document_Number being compared to your passed in nvarchar value. Since it looks like Document_Number is an int, that won't work. You would have to convert the int field to an nvarchar (which will kill your performance). My guess is that you didn't want to do that last comparison. If you did, you are going to need to do the conversion.

Also, the reason why you get a "Command(s) completed successfully" message is because you are altering your stored procedure, not actually running it.

Jinu _George · Answer 2 · 2012-09-02T19:13:00

Solution 3

Tim is right..

check database field types else
please give your table structure..

Posted 2-Sep-12 19:13pm

Jinu _George

Kuthuparakkal · Answer 3 · 2012-09-02T19:07:00

Solution 2

SQL

WHERE (DocumentCategory LIKE '''%'+@Category+'%''' OR Document_Name LIKE '''%'+@Category+'%''' OR Document_Number LIKE '''%'+@Category+'%''')

Posted 2-Sep-12 19:07pm

Kuthuparakkal

prashant patil 4987 · Answer 4 · 2012-09-02T20:51:00

Solution 4

Check your WHere Condition

SQL

WHERE (DocumentCategory LIKE '%'+@Category+'%' OR Document_Name LIKE '%'+@Category+'%' OR Document_Number LIKE '%'+@Category+'%'))

and also check "Document_Number" this column Data type (Int or nvarchar).

Posted 2-Sep-12 20:51pm

prashant patil 4987