Writing SQL Insert Function

Writing SQL Insert Function

I’m trying to create SQL function which would add entry to a table. Before adding a new user I would like to check or this user isn’t in a table already. I wrote some code, but can’t save it as I get error: Invalid use of a side-effecting opreator ‘INSERT’ within function. The last statement included within a 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 [email protected] and [email protected]))
            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 beginner with SQL so any advice would be great!

In short, Functions are not allowed to make any changes to ANY SQL Server object. Stored procedures can. With minor changes, your code is now an 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 [email protected] and [email protected]))
            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 thus:

exec dbo.CreateUser 'Jim', 'Teddy', '[email protected]', 'Where', 'Here';
.
.
.
.