CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Nguyen Minh Tuan
Nguyen Minh Tuan

Reputation: 57

How to use like in stored procedure with int parameter?

I 've used the Query Builder tool of Visual Studio 2008 to build a stored procedure. This is the preview script:

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!

Upvotes: 0

Views: 23053

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

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.

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

declare @studentid int set @studentid = 205

;with tmp as (select 201102050001 studentid)
select * from tmp
WHERE StudentID LIKE '%' + RIGHT(@StudentID,12) + '%'

-> output: 201102050001

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453717

You want to find rows where @StudentID is a substring of StudentID? If so

WHERE StudentID LIKE '%' + cast(@StudentID as varchar(10)) + '%'

Should work.

Upvotes: 7

Pradeep
Pradeep

Reputation: 3276

'%' can not be used with integer data type. Why would you need '%' when you have absolute studentID? Just compare it with '='.

If you really insist on using '%', then you have to change data type of studentID to varchar or something like that.

Upvotes: 1

alex
alex

Reputation: 3720

One easy way to go around this would be to convert StudentID to a varchar. Just use CONVERT(varchar, StudentID).

Upvotes: 0

Related Questions