Writing an SQL Insert Function

Question

I am trying to create an SQL function that will add a record to a table. Before adding a new user, I would like to check whether this user is no longer in the table. I wrote the code, but I can’t save it, because I get the error: Invalid use of a third-party opreator 'INSERT' inside the function. The last statement included in the function must be a return statement.

CREATE FUNCTION [dbo].[CreateUser] ( @Username varchar(20), @Password varchar(20), @Email varchar(50), @PasswordQuestion varchar(30), @PasswordAnswer varchar(30) ) RETURNS bit/* datatype */ AS BEGIN if (Exists(Select Username from Users where Username=@Username and Password=@Password )) return 1; else begin INSERT INTO dbo.Users (Username, Password, Email, UserId, IsApproved, IsLockedOut, IsOnline, CreationDate, PasswordQuestion, PasswordAnswer) VALUES (@Username, @Password, @Email, 1, 0, 0, 0, GetDate(), @PasswordQuestion, @PasswordAnswer); return 0; end END 

I'm just starting with SQL, so any advice would be great!

Answer 1

In short, functions are not allowed to make any changes to any SQL Server object. Stored procedures can. With a few changes, your code is now SP.

 CREATE PROC [dbo].[CreateUser] ( @Username varchar(20), @Password varchar(20), @Email varchar(50), @PasswordQuestion varchar(30), @PasswordAnswer varchar(30) ) --RETURNS bit/* datatype */ AS BEGIN if (Exists(Select Username from Users where Username=@Username and Password=@Password )) return 1; else begin INSERT INTO dbo.Users (Username, Password, Email, UserId, IsApproved, IsLockedOut, IsOnline, CreationDate, PasswordQuestion, PasswordAnswer) VALUES (@Username, @Password, @Email, 1, 0, 0, 0, GetDate(), @PasswordQuestion, @PasswordAnswer); return 0; end END GO 

And you can call it that:

 exec dbo.CreateUser 'Jim', 'Teddy', ' [e-mail] ', 'Where', 'Here';