Mysql – Stored Procedure Not working as Expected

MySQLstored-procedures

When I create the stored procedure without parameter it works as expected, but when I create it with parameter it doesn't return any row.

DELIMITER //

CREATE PROCEDURE search()
BEGIN 
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* GS Road.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* GS Road.*$'));

END//

The above procedure returns the rows.

But this doesn't return any value

DELIMITER //

CREATE PROCEDURE search(IN **qry** varchar(100))
BEGIN 
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* ***qry**.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* ***qry**.*$'));

END//

Best Answer

That's because you're looking for the string 'qry' instead of the value held by the parameter qry.

Replace:

REGEXP ('^.* qry.$'))

With

REGEXP CONCAT('^.* ',qry, '.$'))

Related Solutions

Sql-server – How to optimize this dynamic stored procedure

I'm going to skip past your questions and try to offer broader guidelines/advice instead.

The definitive/canonical guide to dynamic SQL, the situations where it is applicable and where it can be avoided, is Erland Sommarskog's Dynamic Search Conditions in T-SQL. Read it, re-read, run through Erland's examples, make sure you understand the reasoning behind the recommendations.

You're dealing with a fairly common scenario and the approach you've taken is not unusual. A couple of points worth highlighting:

Using temporary tables is probably unnecessary. Is there a reason they were introduced?
You have probably over-indexed the table. Read Kimberly Tripp's "just because you can, doesn't mean you should" article on the topic.
Because you've over-indexed on individual columns, you're probably lacking good covering indexes. With so many aggregations and such a wide range of search conditions, these will be a challenge to get right.

Now the most important part of getting these kinds of searches right... apply the 80/20 rule.

The majority of calls to your procedure are likely to comprise a relatively small number of the possible variations of parameters. You cannot create optimal indexes for all combinations of 15 parameters, so identify the most common patterns, create static stored procedures for these and index for them appropriately. Deal with the remaining combinations with dynamic SQL, following Erland's best practices.

In these scenarios, you will often find the usage patterns closer to 95/5 than 80/20 so the additional work of creating static procedures is not as labour intensive as it seems at first glance.

SQL Server – Stored Procedure Not Showing Results

You need to split your parameters into rows and use where ... in as a check.

Using a split function like this one your code could look like this.

CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS 
BEGIN

    DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))

    INSERT INTO @ProjHomeGrpTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHomeGrp);

    DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))

    INSERT INTO @ProjHierTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHier);

    SELECT  [Capital Project] ,
            [Capital Task] ,
            ResourceName ,
            ProjectName ,
            [Project Home Group] ,
            ActualWork ,
            TimeByDay ,
            ResourceStandardRate ,
            ActualWork * ResourceStandardRate AS Dollars ,
            [Project Hierarchy]
    FROM    [IR.CapOnly]
    WHERE   ( TimeByDay >= @StartDate )
            AND ( [Project Home Group] in (SELECT Value FROM @ProjHomeGrpTbl) )
            AND ( TimeByDay <= @EndDate )
            AND ( ActualWork > 0 )
            AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
    ORDER BY ProjectName ,
            ResourceName

END

Best Answer

Related Solutions

Sql-server – How to optimize this dynamic stored procedure

SQL Server – Stored Procedure Not Showing Results

Related Question