how to rewrite the code of MSSQL server Stored Procedure to mysql stored procedure..

Question

0.00/5 (No votes)

See more:

how to rewrite the code of MSSQL server Stored Procedure to Mysql stored procedure..

below is my stored procedure for login in mssql..

SQL

create procedure sp_login(@uid int out,@uname varchar(30),@pwd varchar(30) 
as
    if exists(select * from reg where uname=@uname and pwd=@pwd)
        begin
            select  @uid=uid from reg where uname=@uname and pwd=@pwd
            return 0
        end
    else
        begin
            return 1
        end

Posted 5-Jun-13 20:34pm

abey e mathews

Updated 5-Jun-13 20:39pm

Prasad Khandekar

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Accepted Answer · 2013-06-05T20:57:00

Solution 1

Hello,

Following code shows the MySQL version of your stored procedure. Stored procedures don't return a value as opposed to stored functions.

SQL

DROP PROCEDURE IF EXISTS sp_login;
CREATE FUNCTION sp_login (OUT po_uid INT, pi_uname VARCHAR(30), pi_pwd VARCHAR(30))
BEGIN
    SELECT uid INTO po_uid 
    FROM reg 
    WHERE uname = pi_uname AND pwd = pi_pwd;
END;

Regards,

Posted 5-Jun-13 20:57pm

Prasad Khandekar

Comments

abey e mathews 6-Jun-13 4:08am

is this

DELIMITER $$

CREATE PROCEDURE `database_name`.`sp_login` (OUT po_uid INT, pi_uname VARCHAR(30), pi_pwd VARCHAR(30))
BEGIN
SELECT uid INTO po_uid
FROM reg
WHERE uname = pi_uname AND pwd = pi_pwd;
END

abey e mathews 6-Jun-13 4:10am

is any alternate way for "return 0"

Prasad Khandekar 6-Jun-13 6:49am

You can change select as follows

SELECT COALESCE(uid, 0) INTO po_uid
FROM reg
WHERE uname = pi_uname AND pwd = pi_pwd;