Jump to content

Storing Results Temporary

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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...