syntax to pass parameter in like operator using stored procedure

[rocksoft]

Hi,

i'm using mysql database, i don't know syntax how to pass the parameter in like operator inside the stored procedure, i want to select the store number related to textbox input number given by user, Eg: if they type "3" we have to select which stores starts with "3", i got syntax error when executing the following stored procedure, please correct this sp and solve my problem
Advanced thanks for any reply.

CREATE PROCEDURE HD_SELECT_STORES(IN STORENUM INT)
BEGIN
select storeno from hd_stores_login where storeno like '"STORENUM"%';
END;

Thanks.

[sjsaran12]

Plz try this

CREATE PROCEDURE HD_SELECT_STORES
@STORENUM int
as
BEGIN
select storeno from hd_stores_login where storeno like @STORENUM;
END;

while Executing this stored procedure:

EXECUTE HD_SELECT_STORES 'Value%'


You can pass the wildcard while you execute the stored procedure, but u cannot give wildcard while creating it. So add the wildcard with the data you pass the parameter value.

[Charanpreet]

[quote=sjsaran12]

Plz try this, Better Solution

CREATE PROCEDURE HD_SELECT_STORES
@STORENUM int
as
BEGIN
select storeno from hd_stores_login where storeno like '%'+@STORENUM+'%';
END;

[sig0]

Try LIKE CONCAT(STORENUM, '%')

[SLauren]

CREATE PROCEDURE HD_SELECT_STORES(IN STORENUM INT)
BEGIN
SET @pattern = CONCAT('%',STORENUM,'%');
SET @sql = 'select storeno from hd_stores_login where storeno like ?';
PREPARE stmt_name FROM @sql;
EXECUTE stmt_name USING @pattern;
DEALLOCATE PREPARE stmt_name ;

END;

Hope this could help.

Thanks,
Lauren

[Francisco Lemos]

In case needing to pass more than one parameter:
/*** courtesy of Lauren's code **/

CREATE PROCEDURE HD_SELECT_STORES(IN STORENAME TEXT, IN STORENUM INT )
BEGIN
SET @pattern1 = CONCAT('%',STORENAME,'%');
SET @pattern2 = STORENUM;
SET @sql = 'select storeno from hd_stores_login where storename like ? AND stroreno = ?';
PREPARE stmt_name FROM @sql;
EXECUTE stmt_name USING @pattern1, @pattern2;
DEALLOCATE PREPARE stmt_name ;
 
END;
 

Cheers
Francis

[Mackson]

Use the LEFT SQL function to get a number of characters on the left of the parameter. How many characters? you're wondering. I say all the characters. How so? Use the LEN SQL function to get the length of the parameter.


Example:

CREATE PROCEDURE dbo.OvertimeSearchName
 
    (
 
    @LastName nchar(200)
    )
 
AS
 
BEGIN
SELECT  EmployeeNumber,FirstName + ' ' + LastName as FullName
FROM Employee 
/* Pay attention here */
WHERE    LastName like (left(@LastName,LEN(@LastName)))+'%' ;
 
END
 
--exec dbo.OvertimeSearchName '%Thakwalakwa%'
 
 
RETURN