CODEWITHSUNDEEP
sqlsql-server-2008variablesstored-proceduressql-like
AToya
AToya

Reputation: 575

Using Stored Procedure variable in Like statement

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 + '%'

Upvotes: 2

Views: 7921

Answers (4)

Steves Kamdem
Steves Kamdem

Reputation: 1

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

Upvotes: 0

Koen
Koen

Reputation: 140

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();

Upvotes: 0

vee
vee

Reputation: 38645

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, '')

Upvotes: 0

Robert
Robert

Reputation: 25763

This works for me:

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

Sql Fiddle DEMO

Upvotes: 4

Related Questions