boen_robot Posted July 25, 2009 Share Posted July 25, 2009 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 More sharing options...
aalbetski Posted July 25, 2009 Share Posted July 25, 2009 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 Link to comment Share on other sites More sharing options...
boen_robot Posted July 25, 2009 Author Share Posted July 25, 2009 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.