Writing SQL Insert Function

Question

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 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 beginner with SQL so any advice would be great!

SQL Functions cannot make changes to the database. You'll need to use a stored procedure instead. — RBarryYoung, May 10 '13 at 01:45

RichardTheKiwi · Accepted Answer · 2013-05-10T01:52:36.847

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 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 thus:

exec dbo.CreateUser 'Jim', 'Teddy', 'jim@do.not.email', 'Where', 'Here';

Writing SQL Insert Function

1 Answers1