SQL 如何在带有int参数的存储过程中使用like？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4905745/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use like in stored procedure with int parameter?
提问by Nguyen Minh Tuan
I 've used the Query Builder tool of Visual Studio 2008 to build a stored procedure. This is the preview script:
我已经使用 Visual Studio 2008 的 Query Builder 工具来构建存储过程。这是预览脚本：
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = 'SelectQuery' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.SelectQuery
GO
CREATE PROCEDURE dbo.SelectQuery
(
@StudentID int
)
AS
SET NOCOUNT ON;
SELECT StudentID, StudentName, StudentPhone, StudentAddress,
StudentBirthDay, StudentDescription, StudentStatus
FROM tbl_Student
WHERE (StudentID LIKE '%' + @StudentID + '%')
GO
But when I tried to execute it, I got an error:
但是当我尝试执行它时，出现错误：
Error Message: Conversion fail when converting the value '%' to datatype int.
Please help me!
请帮我！
回答by Martin Smith
You want to find rows where @StudentIDis a substring of StudentID? If so
您想查找?@StudentID的子字符串所在的行 StudentID如果是这样的话
WHERE StudentID LIKE '%' + cast(@StudentID as varchar(10)) + '%'
Should work.
应该管用。
回答by RichardTheKiwi
I think you actually need this
我想你真的需要这个
WHERE RIGHT(StudentID,12) LIKE '%' + RIGHT(@StudentID,12) + '%'
In case you are wondering, RIGHT implicitly converts non-varchar data to varchar. A more proper way would be to use CONVERT explicitly, but as you can see, it is longer.
如果您想知道，RIGHT 会将非 varchar 数据隐式转换为 varchar。更合适的方法是显式使用 CONVERT，但正如您所见，它更长。
WHERE (VARCHAR(20),StudentID) LIKE '%' + CONVERT(VARCHAR(20),@StudentID) + '%'
As Martin points out, LIKE implicitly converts the LHS. Don't think I ever tried it that way but it works
正如 Martin 指出的那样，LIKE 隐式转换了 LHS。不要以为我曾经这样尝试过，但它有效
declare @studentid int set @studentid = 205
;with tmp as (select 201102050001 studentid)
select * from tmp
WHERE StudentID LIKE '%' + RIGHT(@StudentID,12) + '%'
-> output: 201102050001
回答by Pradeep
'%' can not be used with integer data type. Why would you need '%' when you have absolute studentID? Just compare it with '='.
'%' 不能与整数数据类型一起使用。当您拥有绝对的学生 ID 时，为什么还需要 '%'？只需将其与“=”进行比较。
If you really insist on using '%', then you have to change data type of studentID to varchar or something like that.
如果您真的坚持使用'%'，那么您必须将studentID 的数据类型更改为varchar 或类似的内容。
回答by alex
One easy way to go around this would be to convert StudentID to a varchar. Just use CONVERT(varchar, StudentID).
解决此问题的一种简单方法是将 StudentID 转换为 varchar。只需使用CONVERT(varchar, StudentID).
