执行存储过程时出错

问题描述：

我给出了这样的存储过程:

I gave a stored procedure like this:

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..

Command(s) completed successfully.

直到这里一切都OK.但是当我从执行存储过程中添加@category值时，出现错误

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

答

您的错误消息告诉您问题出在哪里.在WHERE语句中，您将Document_Number与传入的nvarchar值进行比较.由于看起来Document_Number是int，因此将无法正常工作.您必须将int字段转换为nvarchar(这会降低性能).我的猜测是您不想做最后的比较.如果这样做，则需要进行转换.

另外，获得成功完成命令"消息的原因是因为您正在更改存储过程，而不是实际运行它.

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.

Tim是正确的. >
检查数据库字段类型else
请给出您的表结构.

Tim is right..

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

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

执行存储过程时出错

相关推荐