MySQL stored procedure vs function, which would I use when?

I'm looking at MySQL stored procedures and function. What is the real difference?

They seem to be similar, but a function has more limitations.

I'm likely wrong, but it seems a stored procedure can do everything and more a

 stored function can. Why/when would I use a procedure vs a function?

 Answers

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.

e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a 

procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.

---

One significant difference is that you can include a function in your SQL queries, 

but stored procedures can only be invoked with the CALL statement:

UDF Example:

CREATE FUNCTION hello (s CHAR(20))
   RETURNS CHAR(50) DETERMINISTIC
   RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE names (id int, name varchar(20));
INSERT INTO names VALUES (1, 'Bob');
INSERT INTO names VALUES (2, 'John');
INSERT INTO names VALUES (3, 'Paul');

SELECT hello(name) FROM names;
+--------------+
| hello(name)  |
+--------------+
| Hello, Bob!  |
| Hello, John! |
| Hello, Paul! |
+--------------+
3 rows in set (0.00 sec)

Sproc Example:

delimiter //

CREATE PROCEDURE simpleproc (IN s CHAR(100))
BEGIN
   SELECT CONCAT('Hello, ', s, '!');
END//
Query OK, 0 rows affected (0.00 sec)

delimiter ;

CALL simpleproc('World');
+---------------------------+
| CONCAT('Hello, ', s, '!') |
+---------------------------+
| Hello, World!             |
+---------------------------+
1 row in set (0.00 sec)

List of Stored Procedures/Functions Mysql Command Line

How can I see the list of the stored procedures or stored functions in mysql command line like show tables; or show databases; commands.

 Answers

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

---

For view procedure in name wise

select name from mysql.proc 

below code used to list all the procedure and below code is give same result as show procedure status

select * from mysql.proc 

---

As mentioned above,

show procedure status;

Will indeed show a list of procedures, but shows all of them, server-wide.

If you want to see just the ones in a single database, try this:

SHOW PROCEDURE STATUS WHERE Db = 'databasename';

---

My preference is for something that:

1. Lists both functions and procedures,
2. Lets me know which are which,
3. Gives the procedures' names and types and nothing else,
4. Filters results by the current database, not the current definer
5. Sorts the result

Stitching together from other answers in this thread, I end up with

select 
  name, type 
from 
  mysql.proc 
where 
  db = database() 
order by 
  type, name;

... which ends you up with results that look like this:

mysql> select name, type from mysql.proc where db = database() order by type, name;
+------------------------------+-----------+
| name                         | type      |
+------------------------------+-----------+
| get_oldest_to_scan           | FUNCTION  |
| get_language_prevalence      | PROCEDURE |
| get_top_repos_by_user        | PROCEDURE |
| get_user_language_prevalence | PROCEDURE |
+------------------------------+-----------+
4 rows in set (0.30 sec)

---

To show just yours:

SELECT
  db, type, specific_name, param_list, returns
FROM
  mysql.proc
WHERE
  definer LIKE
  CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'));

---

SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='database_name'

---

                           show procedure status;

using this command you can see the all procedures in databases

---

Use the following query for all the procedures:

select * from sysobjects 
where type='p'
order by crdate desc

List stored procedures in MySQL

This post shows how to get a complete list of stored procedures in a MySQL database and then to see what code is used in the stored procedure.

List MySQL stored procedures

Run the following SQL query either from the MySQL command line, or using a GUI tool like phpMyAdmin to get a complete list of stored procedures from all databases your login has access to:

SHOW PROCEDURE STATUS

To just list procedures from a particular database do this, where we want to query stored procedures from the "mydb_abc" database:

SHOW PROCEDURE STATUS WHERE Db = 'mydb'

The output from the above commands will look something like this:

+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name    | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mydb | mysproc | PROCEDURE | root@%  | 2011-08-18 20:29:53 | 2011-08-18 20:29:53 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

Show the SQL code in the stored procedure

Use the show create procedure query to get the SQL code from the query. To get it for the "mysproc" stored procedure in the example output above, do this:

SHOW CREATE PROCEDURE mysproc

This then gives you a few fields as a resultset and it's the "Create Procedure" column which has the procedure creation SQL.

Related posts:

• Listing MySQL tables with PHP
• Listing tables and their structure with the MySQL Command Line Client
• Using the MySQL command line tool

The stored procedure returns all records, but expects that there will be a record in mysql

This is my query for creating store procedure:

CREATE PROCEDURE `GetAccountDetails`(IN `Accountnumber` VARCHAR(50), IN `casshieldId` VARCHAR(50), IN `transactionbefore` DOUBLE, IN `sourceofTransaction` vARCHAR(50))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
SELECT * FROM account WHERE AccountNumber = Accountnumber AND casShieldId = casshieldId AND transactionBefore = transactionbefore AND sourceOfTransaction = sourceofTransaction;
END

When I call the store procedure:

call GetAccountDetails("Accountnumber","casshieldId ",transactionbefore,"sourceofTransaction");

It returns all records in that account table.

How can I fix this?

---

Recreate your procedure like this:

 CREATE PROCEDURE `GetAccountDetails`(IN `Accountnumber1` VARCHAR(50), IN `casshieldId1` VARCHAR(50), IN `transactionbefore1` DOUBLE, IN `sourceofTransaction1` vARCHAR(50))
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN
    SELECT * FROM account WHERE AccountNumber = Accountnumber1 AND casShieldId = casshieldId1 AND transactionBefore = transactionbefore1 AND sourceOfTransaction = sourceofTransaction1;
    END

The difference is only on the procedure variable name because you put the exact same name with the column name make it always stay true no matter what you pass. The DB will think you compare your column name with the same thing.

Query database that does not use a stored procedure

i have a db diagram as shown above. What I need to do is: Select a table which is as follows: PatientId, Emergency.Name or Doctor Names, Patient.Address.

Explanation; I need A query which will return patient details and, if Patient has an emergency also add an emergency, if not select all doctor.Name into one row.

So, example:

So, the first row was built because EmergencyId in table patient was null, while the second row had an emergency Id.

I need the query to simulate this. Using SSRS

Thanks a lot!

---

Thanks guys, can you at least explain me how to return this data in separate rows, so I can union later on?

I believe this will return the data you want broken out into separate rows, returning Emergency if it exists and the Drs if it does not. Good luck!

SELECT Distinct Coalesce(E.Name, D.Name) as VariableColumn,
       P.PatientId,
       P.Address
FROM Patient P
  LEFT JOIN Emergency E
    ON P.EmergencyId=E.EmergencyId
  LEFT JOIN PatientDoctor PD
    ON P.PatientID=PD.PatientId
  LEFT JOIN Doctor D
    ON PD.DoctorId=D.DoctorId

Mysql The stored procedure does not exist

I create stored procedure from mysql client terminal and everything is OK. But when I try to call it i get this error message:

ERROR 1305 (42000): PROCEDURE XXX does not exist

After that i try to create it again without

DROP PROCEDURE IF EXISTS

statement and I get this:

ERROR 1304 (42000): PROCEDURE XXX already exists

What's wrong?

*THE PROBLEM WAS THAT MY DATABASE HAVE POINT (.) IN NAME *

*EXAMPLE: 'site.db' -> THIS IS WRONG NAME OF DATABASE AND MYSQL CAN'T FIND PROCEDURE !!!*

---

Possibly you have problems with consistency of your system databases after incorrect upgrade or something like that. What are results for

select * from information_schema.ROUTINES where routine_name = 'xxx'




Mysql IF Otherwise in the stored procedures does not work

CREATE PROCEDURE p1
(
    IN name_val VARCHAR(255),
    OUT iJobID  INT
)

BEGIN

    IF NOT EXISTS (SELECT id FROM test WHERE id='11')

        BEGIN

            INSERT INTO test(name) VALUES(name_val);
            SET iJobID :=  LAST_INSERT_ID();
        END

    ELSE
        BEGIN

             UPDATE test SET name=name_val WHERE id = 11;
        END 

    INSERT INTO vasu2(vname) VALUES(name_val);
    SET @ivD :=  LAST_INSERT_ID();

    INSERT INTO vasu(id, id2) VALUES(iJobID, @ivD);
END;

---

the IF syntax you are using is most likely for T-SQL. Also, you need to change the delimiter.

DELIMITER $$
CREATE PROCEDURE p1
(
    IN name_val VARCHAR(255),
    OUT iJobID  INT
)
BEGIN

    SET @recCount := (SELECT COUNT(*) FROM test WHERE id = 11);
    IF @recCount > 0 THEN
        INSERT INTO test(name) VALUES(name_val);
        SET iJobID :=  LAST_INSERT_ID();
    ELSE
        UPDATE test SET name=name_val WHERE id = 11;
    END IF;

    INSERT INTO vasu2(vname) VALUES(name_val);
    SET @ivD :=  LAST_INSERT_ID();

    INSERT INTO vasu(id, id2) VALUES(iJobID, @ivD);
END $$
DELIMITER ;

• Using Stored Procedures

Stored procedure does not work in MySQL

DELIMITER $$

DROP PROCEDURE IF EXISTS `pawn`.`simpleproc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `pawn`.`simpleproc`(OUT param1 int, inout incr int)
BEGIN
declare incr Integer;
    set incr= incr+1;
    SELECT count(*) into param1 FROM pawnamount;
 END $$

This is my code to create a stored procedure....It's created.. For execute..

call simpleproc(@param1,@incr);
select @param1,@incr

The Result will be null values.. It is the simple one.. I've tried many times.But,I get null values only..

---

DECLARE incr INT;                            -- incr is NULL here, add DEFAULT 0  if you want it to have a value
SET incr = incr + 1                          -- NULL + 1 is still NULL
SELECT COUNT(*) INTO param1 FROM pawnamount; -- If the table pawnamount is empty, it generates an empty set, which in a parameter assignment becomes NULL.

The MySQL stored procedure does not work

Can someone help me with why this statement returns NULL ?

DROP PROCEDURE IF EXISTS hr.Test;
CREATE PROCEDURE hr.`Test`
           (
             IN `empID` BIGINT(20)
           , IN untill date
           , IN `salaryType` INT(10)
           )
    MODIFIES SQL DATA
BEGIN
  select untill;
END;

call Test(2, '2014-01', 2);

It's strange since when i want to return value of empID or salaryType it works!!! Any idea ?

Thanks,

---

Simple reason because '2014-01' is an invalid date and so it returns null. To prove that try the below query and it will return null

select cast('2014-01' as date)




Mysql - The stored procedure does not fit in the table

I have a simple stored procedure in a database which is called AddNewStudent:

    CREATE PROCEDURE dbo.AddNewStudent(
    @fName nvarchar(20),
    @sName nvarchar(20),
    @lName nvarchar(20),
    @faculty nvarchar(10),
    @specialty nvarchar(50),
    @OKS smallint,
    @StudentStat smallint,
    @fak nvarchar(50),
    @Course smallint,
    @Porok nvarchar(5),
    @Group int
    )
    AS
    INSERT INTO [Students] (FirstName, SecondName, LastName, Faculty,
    Specialty, OKS, StudentStatus, FakNumber, Course, Potok, [[Group]]])
    VALUES (@fName , @sName, @lName, @faculty, @specialty, @OKS,
    @StudentStat, @fak, @Course, @Porok, @Group)
    RETURN 2;

When i test the procedure through DatabaseExplorer (VS2013) everything is OK and the record is inserted into the table. But when i call the procedure in c# nothing happens. Bellow is the code for the method which I use to call the procedure:

    public static bool InsertStudent (Student student)
    {
        StudentDataClassesDataContext dc = new StudentDataClassesDataContext();
        try
        {
           int returnValue = dc.AddNewStudent(student.FirstName, student.SecondName, student.LastName, student.Faculty, student.Specialty, student.OKS, student.StudentStatus,
                student.FakNumber, student.Course, student.Potok, student._Group_);
            dc.SubmitChanges();
            MessageBox.Show("Return Value : " + returnValue, "Info", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        catch (Exception e)
        {
            MessageBox.Show("Exception : " + e.Message, "Info", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return false;
        }
        return true;
    }

The returned value is 2 which means that the procedure does its work but why there is no record inserted into the table? I read that dc.SubmitChanges() is used instead of Commit.

---

You can start Sqlserver Profiler to see what happens, to see if there is any transaction start without commit? Also you can set a break point just after dc.submitchanges() and when your application hits break point, go to sql server and run this query

Select * from [Students] with (nolock)

and be sure that the data is in your table, after that continue running your application and run that query again, if the data was in your table and no it's gone there is an uncommited transaction. to solve that just use TransactionScope. if data is not in your table from the start you might running your code on another database. You can pass the connnctionstring in datacontext constructor.

Mysql The stored procedure does not always work the same way

We have a specific stored procedure which splits one of the parameters received and performs some inserts based on the split data.

The procedure is working fine but randomly it crashes. We have an audit of the parameters being passed and also an audit of the values that have been split when the procedure was run. For some reason it seems like the split added an extra item at the beginning or sometimes mixes the order of the Split Data which matters a lot in our case as the data being split is formatted something like this UserId#LocationId#Note#RecordId*Date

The strange thing is that if we take the parameters from the audit and re-run the procedure that failed, it works fine!!! This is crashing once every 5000 times that it is run. The SplitString function is below.

ALTER FUNCTION [dbo].[SplitString]
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX))
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1
    BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1  

        INSERT INTO @output (splitdata)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)  

    END
RETURN
END

---

Try this split function instead and see if you have the same issues.

Convert Delimited value to a List

After you compile it just try:

select * from dbo.fnArray('Does#This#Thing#Really#Work', '#')




Mysql - The INSERT stored procedure does not work?

I'm trying to make an insertion from one database called suspension to the table called Notification in the ANimals database. My stored procedure is this:

       ALTER PROCEDURE [dbo].[spCreateNotification]
        -- Add the parameters for the stored procedure here
        @notRecID int,
        @notName nvarchar(50),
        @notRecStatus nvarchar(1),
        @notAdded smalldatetime,
        @notByWho int
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here
        INSERT INTO Animals.dbo.Notification
(
NotRecID,
NotName,
NotRecStatus,
NotAdded,
NotByWho
)
values (@notRecID, @notName, @notRecStatus, @notAdded, @notByWho);
    END

The null inserting is to replenish one column that otherwise will not be filled, I've tried different ways, like using also the names for the columns after the name of the table and then only indicate in values the fields I've got. I know it is not a problem of the stored procedure because I executed it from the sql server management studio and it works introducing the parameters. Then I guess the problem must be in the repository when I call the stored procedure:

public void createNotification(Notification not)
        {
            try
            {
                DB.spCreateNotification(not.NotRecID, not.NotName, not.NotRecStatus,
                                        (DateTime)not.NotAdded, (int)not.NotByWho);

            }
            catch
            {
                return;
            }
        }

And I call the method here:

public void createNotifications(IList<TemporalNotification> notifications)
        {

            foreach (var TNot in notifications)
            {
                var ts = RepositoryService._suspension.getTemporalSuspensionForNotificationID(TNot.TNotRecID);
                Notification notification = new Notification();
                if (ts.Count != 0)
                {
                    notification.NotName = TNot.TNotName;
                    notification.NotRecID = TNot.TNotRecID;
                    notification.NotRecStatus = TNot.TNotRecStatus;
                    notification.NotAdded = TNot.TNotAdded;
                    notification.NotByWho = TNot.TNotByWho;

                    if (TNot.TNotToReplace != 0)
                    {
                        var suspensions = RepositoryService._suspension.getSuspensionsAttached((int)TNot.TNotToReplace);
                        foreach (var sus in suspensions)
                        {
                            sus.CtsEndDate = TNot.TNotAdded;
                            sus.CtsEndNotRecID = TNot.TNotRecID;
                            DB.spModifySuspensionWhenNotificationIsReplaced((int)TNot.TNotToReplace, (int)sus.CtsEndNotRecID, (DateTime) sus.CtsEndDate);
                        }
                        DB.spReplaceNotification((int)TNot.TNotToReplace, DateTime.Now);
                        createNotification(notification);
                    }
                    else
                    {
                        createNotification(notification);
                    }
                }
            }
            deleteTemporalNotifications(notifications);
        }

It does not record the value in the database. I've been debugging and getting mad about this, because it works when I execute it manually, but not when I automatize the proccess in my application. Does anyone see anything wrong with my code?

Thank you

EDIT: Added more code. It still doesn't work changing that, I mean, the procedure works if I execute it, so I don't know what could be the error. In fact, I don't get any error. Could it be a matter of writin in a table that is not in the database where you have your stored procedure?

---

I would specify your column names and DONT incude the NULL at all for that column. Just let SQL Server deal with it.

INSERT INTO Animals.dbo.Notification
(
 RecID,
 [Name],
 RecStatus,
 Added,
 ByWho
)
values (@notRecID, @notName, @notRecStatus, @notAdded, @notByWho);

The value of the return string of the stored procedure does not work

I have created a stored proc that should return a string value based on which action was taken. The queries is all working fine, except I'm getting an error on the @return_value ("Converting varchar value to int)... I tried casting both values withing the query, but it's not working...

The error I'm receiving:

    Conversion failed when converting the varchar value 'Request ID: 454 captured on 2017-04-25 10:16:07' to data type int.

This is my sql code of what I did:

    USE [VehicleManagement]

    GO
    /****** Object:  StoredProcedure [dbo].[spSaveContractsCaptured]
    Script Date: 2017/04/23 8:56:10 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_SaveContractsCaptured]
    @VehicleServiceRequestID int,
    @InvoiceNo nvarchar(50),
    @Amount decimal(18, 2),
    @OdometerReading float,
    @ExpiryDate datetime,
    @ServiceDate datetime,
    @CapturedBy nvarchar(50),
    @CapturedDate datetime,
    @ContractStartDate datetime,
    @PeriodExpiry nvarchar(50),
    @returnVal nvarchar(255) output

    AS
  BEGIN
  SET NOCOUNT ON

  IF NOT EXISTS (SELECT ID FROM [VehicleManagement].[dbo].[VehicleService_Captured]
  WHERE VehicleServiceRequestID = @VehicleServiceRequestID )
  BEGIN
        INSERT INTO [VehicleService_Captured] (VehicleServiceRequestID, InvoiceNo,
        Amount, OdometerReading, ExpiryDate, ServiceDate, CapturedBy, CapturedDate, ContractStartDate, PeriodExpiry)
        VALUES (@VehicleServiceRequestID, @InvoiceNo, @Amount, @OdometerReading, @ExpiryDate, @ServiceDate,
        @CapturedBy, @CapturedDate, @ContractStartDate, @PeriodExpiry) 

        set @returnVal = SCOPE_IDENTITY()
        RETURN CAST(@returnVal AS VARCHAR(255))
    end
else
   SET @returnVal = (SELECT 'Request ID: ' + convert(varchar,ID) +  ' captured on ' + convert(varchar,[CapturedDate],120)  FROM [VehicleManagement].[dbo].[VehicleService_Captured]
    WHERE VehicleServiceRequestID = @VehicleServiceRequestID)

   RETURN CAST(@returnVal AS nvarchar(255))
 END

Is there something I am missing or what am I doing wrong?

---

As Triv pointed in the right direction, you should just set the value of the output variable and remove the RETURN

USE [VehicleManagement]

    GO
    /****** Object:  StoredProcedure [dbo].[spSaveContractsCaptured]
    Script Date: 2017/04/23 8:56:10 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_SaveContractsCaptured]
    @VehicleServiceRequestID int,
    @InvoiceNo nvarchar(50),
    @Amount decimal(18, 2),
    @OdometerReading float,
    @ExpiryDate datetime,
    @ServiceDate datetime,
    @CapturedBy nvarchar(50),
    @CapturedDate datetime,
    @ContractStartDate datetime,
    @PeriodExpiry nvarchar(50),
    @returnVal nvarchar(255) output

    AS
  BEGIN
  SET NOCOUNT ON

  IF NOT EXISTS (SELECT ID FROM [VehicleManagement].[dbo].[VehicleService_Captured]
  WHERE VehicleServiceRequestID = @VehicleServiceRequestID )
  BEGIN
        INSERT INTO [VehicleService_Captured] (VehicleServiceRequestID, InvoiceNo,
        Amount, OdometerReading, ExpiryDate, ServiceDate, CapturedBy, CapturedDate, ContractStartDate, PeriodExpiry)
        VALUES (@VehicleServiceRequestID, @InvoiceNo, @Amount, @OdometerReading, @ExpiryDate, @ServiceDate,
        @CapturedBy, @CapturedDate, @ContractStartDate, @PeriodExpiry) 

        set @returnVal = SCOPE_IDENTITY()
    END
ELSE
   SET @returnVal = (SELECT 'Request ID: ' + convert(varchar,ID) +  ' captured on ' + convert(varchar,[CapturedDate],120)  FROM [VehicleManagement].[dbo].[VehicleService_Captured]
    WHERE VehicleServiceRequestID = @VehicleServiceRequestID)

 END

And when you call the procedure you should declare a variable and use it with "out" like:

declare @message nvarchar(255)
exec [dbo].[sp_SaveContractsCaptured] //list parameteres, @returnval = @message out

Also be sure that 255 is enough for your message. Hope it helps, cheers!

MySQL Stored Procedure Does Not Work

I'm very new to MySQL stored procedures, and got the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE v_lang TINYINT(1) DEFAULT '1'; DECLARE cursor_lang CURSOR FOR SELECT `l' at line 7

When trying to build this stored procedure:

DELIMITER //
CREATE PROCEDURE UpdateUser(IN p_uid INT(11))
BEGIN

DECLARE v_last_login    TIMESTAMP   DEFAULT '2012-01-01 00:00:00' ;
SELECT `last_login`  INTO v_last_login FROM `user` WHERE `id`= p_uid;

DECLARE v_lang  TINYINT(1)  DEFAULT '1';

DECLARE cursor_lang CURSOR FOR SELECT `l_id` INTO v_lang FROM `user_lang` WHERE `user_id` = p_uid LIMIT 0 , 5;

DECLARE no_more_l   TINYINT(1)  DEFAULT 0;
DECLARE  CONTINUE HANDLER FOR NOT FOUND SET  no_more_l = 1;

OPEN cursor_lang;

FETCH  cursor_lang INTO v_lang_str;
REPEAT 

UPDATE user SET `last_login`=CURRENT_TIMESTAMP() WHERE `id`=p_uid AND `l_id` = v_lang_str;

 UNTIL  no_more_l = 1
 END REPEAT;
 CLOSE  cursor_lang;

END //
DELIMITER ;

What do I do wrong?

---

Are you sure it's ok to use a string as the default for a number variable?

try

DECLARE v_lang  TINYINT(1)  DEFAULT 1;