Click here to Skip to main content
15,662,459 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I gave a stored procedure like this:

ALTER PROCEDURE [dbo].[RecordManagement_getCollgeLocation]
   @Category nvarchar(100)
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..
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
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
Updated 2-Sep-12 17:22pm

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.
Share this answer
Member 9291223 2-Sep-12 23:47pm    
But actually my document_Number is nvarchar(50).still i cnt figure it out as its not working
Tim Corey 2-Sep-12 23:52pm    
Then it is one of your other fields. Check Loc_ID and College_ID first but then also check DocumentCategory as well.
Tim is right..

check database field types else
please give your table structure..
Share this answer
WHERE (DocumentCategory LIKE '''%'+@Category+'%''' OR Document_Name LIKE '''%'+@Category+'%''' OR Document_Number LIKE '''%'+@Category+'%''')
Share this answer
Check your WHere Condition
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).
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