Using Stored Procedure variable in Like statement

Question

I can't seem to properly use the LIKE statement using a variable from a stored procedure. I want to find all the rows from a table that start with the variable passed.

I am currently using the following query where @id is the value passed to the stored procedure as nvarchar(20). This works fine when the IDs completely match, but does not properly use the '%' appended. What is the proper way to complete this task?

SELECT * FROM Table WHERE id LIKE @id + '%'

Is id a string (or some form of it, varchar, text, etc), or an integer (bigint, int, etc)? — Steven V
– Steven V, Commented Jul 17, 2013 at 20:45

Robert · Accepted Answer · 2013-07-17 20:51:31Z

4

This works for me:

declare @id nvarchar(20)
select @id = '1'
SELECT * FROM tab WHERE id LIKE @id + '%'

Sql Fiddle DEMO

answered Jul 17, 2013 at 20:51

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AToya Over a year ago

I fixed the problem. The LIKE wasn't what was wrong. Thanks for the help though!

vee · Accepted Answer · 2013-07-17 20:57:11Z

0

The query doesn't work if @id is null. So what you can do is set it to empty string if @id is null. Please try the following:

begin 
    declare @id nvarchar(20)
    set @id = isnull(@id, '')
    select * from table where id like @id + '%'
end

So in your procedure, adding the following line should work for your query:

set @id = isnull(@id, '')

answered Jul 17, 2013 at 20:57

vee

38.7k7 gold badges76 silver badges80 bronze badges

Comments

Koen · Accepted Answer · 2015-09-23 11:28:38Z

0

Simple solution:

$search_q = '%' . $this_search_q. '%';

$stmt = $db->prepare("SELECT * FROM tbl_tablename WHERE tablecollumn LIKE :id");
$stmt ->bindParam(":id", $search_q );
$stmt->execute();

OR

$search_q = '%' . $this_search_q. '%';

$stmt = $db->prepare("SELECT * FROM tbl_tablename WHERE tablecollumn LIKE $search_q");
$stmt->execute();

answered Sep 23, 2015 at 11:28

Koen

1401 silver badge10 bronze badges

Comments

Pang · Accepted Answer · 2017-01-16 07:58:02Z

CREATE PROCEDURE `SP_GENRE_SELECT`(
    IN _Id INTEGER,
      IN _Name VARCHAR(50),
      IN _account VARCHAR (50),
      IN _Password VARCHAR (50),
      IN _LastConnexionDate DATETIME,
      IN _CreatedDate DATETIME,
      IN _UpdatedDate DATETIME,
      IN _CreatedUserId INTEGER,
      IN _UpdatedUserId INTEGER,
      IN _Status TINYINT
    )
BEGIN
      SELECT *
      FROM user
      WHERE Id LIKE _id IS NULL + '%',CAST(_Id AS VARCHAR)
      AND
      WHERE Name LIKE _Name IS NULL + '%' ,'%',CONCAT('%',_Name,'%')
      AND
      WHERE Account  LIKE _Account IS NULL + '%' ,'%',CONCAT('%',_Account,'%')
      AND
      WHERE LastConnexionDate LIKE _LastConnexionDate IS NULL + '%' ,'%',CONCAT('%',CAST(LastConnexionDate AS VARCHAR(50),'%'))
      AND
      WHERE CreatedDate LIKE _CreatedDate IS NULL + '%' ,'%',CONCAT('%',CAST(_CreatedDate AS VARCHAR(50),'%'))
      AND
      WHERE UpdatedDate LIKE _UpdatedDate IS NULL + '%' ,'%',CONCAT('%',CAST(_UpdatedDate AS VARCHAR(50),'%'))
      AND
      WHERE CreatedUserID LIKE _CreatedUserID IS NULL +'%' ,'%',CONCAT('%',CAST(_CreatedUserID AS VARCHAR(50),'%'))
      AND
      WHERE UpdatedUserID LIKE _UpdatedUserID IS NULL +'%' ,'%',CONCAT('%',CAST(_UpdatedUserID AS VARCHAR(50),'%'))
      AND
      WHERE Status LIKE _Status IS NULL + '%' ,'%',CAST(_Status AS VARCHAR(50),'%');

END

Collectives™ on Stack Overflow

Using Stored Procedure variable in Like statement

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related