Mysql – create multi valued variable inside thesql stored procedure

MySQLparameterstored-procedures

I have two IN variables into my MYSQL stored procedure. I pass one into a select statement and the value is taken in a temporary variable which is passed to the second SQL statement.

CREATE PROCEDURE `usp_mystoredproc`(IN sText varchar(25), IN myId int(21))
begin

set @myCid= ( SELECT tabId AS id                    
FROM            table1
WHERE           IFNULL(tabId , '') != '' and table1_id= myId);

SELECT  searchItem 
FROM    table2 
WHERE  searchItem LIKE CONCAT(sText , '%') and table2_id =  @myCid
LIMIT                   15 ;
END

Now the issue is, in the above stored procedure, @myCid gets multiple values and it fails.
I need to get all those ids and pass them to the second SQL. How do I handle this?

Best Answer

Join table1 and table2. Something like:

SELECT  t2.searchItem 
FROM    table2 as t2
JOIN    table1 as t1
    ON t1.tabid = t2.table2_id
WHERE   t2.searchItem LIKE CONCAT(sText , '%') 
  AND IFNULL(tabId , '') != '' and t1.table1_id= myId
LIMIT                   15 ;

Bottom line, you don't need the @myCid variable

Related Solutions

Mysql – Subquery or multiple left joins, or both

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name 
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;

Sql-server – Can’t cast a stored procedure parameter

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

CREATE PROCEDURE dbo.foo
  @dt DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;
END
GO

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;

Results:

----
14

Best Answer

Related Solutions

Mysql – Subquery or multiple left joins, or both

Sql-server – Can’t cast a stored procedure parameter

Related Question