CODEWITHSUNDEEP
asp.netmysqlsqlstored-procedures
user1082821
user1082821

Reputation: 63

How to have a MySQL procedure return a boolean?

I want to create a procedure that takes in a string, searches the table and the specified column for that string, and returns a 1 if it finds it and a zero if it does not. I am relatively new to SQL and do not know the syntax or commands very well. I want something like this:

CREATE PROCEDURE dbo.GetUsername

(
@Username   NCHAR(10)
)

AS   
    @boolVariable
SELECT  @Username FROM Accounts  
RETURN  @boolVariable

Upvotes: 0

Views: 3301

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56377

I'm not sure if you're looking for mysql or mssql solution.

delimiter //
drop procedure if exists search_string //
create procedure search_string (in str varchar(100))
begin
declare b,r bool;
select count(*) into r from your_table where your_field = str;
if r > 0 then
set b = 1;
else 
set b = 0;
end if;
select b;
end; //
delimiter //

call search_string('searched_string');

Upvotes: 0

p.campbell
p.campbell

Reputation: 100587

You don't return a value, but instead supply that in a result set.

CREATE PROCEDURE GetUsername
(
   @Username   NCHAR(10)
)    
AS       
    SELECT Username FROM Accounts WHERE Username = @UserName;

In your calling code, simply check for the existence of a row in the result set.

Upvotes: 1

Related Questions