Jump to content

Storing Results Temporary


boen_robot
 Share

Recommended Posts

I'm currently trying to make a database, along with a few prepared statements and functions for the sake of better performance.One such function I want is a function that checks if a username is taken by looking at the "users" table. The function itself returns a boolean value.I though it would be as trivial as:

CREATE FUNCTION `db`.`user_exists` (uname VARCHAR(45)) RETURNS BOOLBEGIN	SELECT username FROM users WHERE username = uname;	RETURN FOUND_ROWS() != 0;END

But I get "Not allowed to return a result set from a function" error. With some investigation, I found out that the cause is the SELECT statement. Apparently, even though I don't return it, it's considered to be returned.I tried to instead use:

SELECT SQL_CALC_FOUND_ROWS username FROM users WHERE username = uname LIMIT 0;

hoping for "LIMIT 0" to remove the result set and for SQL_CALC_FOUND_ROWS to get me the FOUND_ROWS(), but the result is the same.Is there a way I can do the same kind of a check without using a SELECT statement? Or any way I can prevent the SELECT statement from returning, and still get the FOUND_ROWS()? I know I can do it on the PHP side with a separate query, but I'd like to have this on the SQL side.

Link to comment
Share on other sites

I don't know mySQL syntax, but the function in SQL Server would be this. It sets a bit (SQL Server does not have a Boolean type) to 0 (fasle) or 1 (true) if the name exists and returns the bit. I'm sure that you will have no problem modifying for mySQL

create function UserExist(@uname varchar(45))returns bitbegin declare @return bit	set @return = 0 --assume false	if exists(SELECT username FROM users WHERE username = @uname)	begin set @return = 1	end	return @returnend

Edited by aalbetski
Link to comment
Share on other sites

The EXISTS() function made the difference. I had never heared of it. Thanks.For anyone interested, here's what I ended up with:

CREATE FUNCTION `db`.`user_exists` (uname VARCHAR(45)) RETURNS BOOLBEGIN	RETURN EXISTS(SELECT username FROM users WHERE username = uname);END

Which is actually even more trivial (as in "fewer lines" trival) than what I expected.

Edited by boen_robot
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...