No longer updated: new and updated blog (including old content) at http://roy-t.nl

Archive for http://roy-t.nl, please update your bookmarks

Fun with stored procedures: insert only if unique. (Transact-SQL/MS SQL)

Posted by Roy Triesscheijn on Tuesday 10 March, 2009

As I’ve said in my previous post stored procedures are allot more powerful than I thought. And really have their own scripting (compiled scripts that is) access to the database.

Stored procedures are often used to enhance the speed of queries that are performed very often, however modern database systems nowadays track queries and store/compile queries that are performed allot of times. So what are the modern day uses for stored procedures?

Stored procedures:

-Save roundtrips  (if you want to do a query based on the result of another query, you can compact those two queries in one).

-Can batch work (if you always do 2 queries at the same time you can call them in only one trip to the database).

-Are Save (in most modern database systems stored procedures are called using parameterized queries or special objects that already wrap/escape dangerous user input).

Can put extra constrains on data (first check if some other value in the database is not interfering with the soon to be executed query)

Can synchronize across multiple applications (stored procedures can lock fields/rows/columns/tables for a short amount of time for better concurrency support)

Today I’m going to show a very simple stored procedure written in Transact SQL Microsoft and Sybase’s proprietary SQL dialect, and C# that hopefully demonstrates all except for the last of the previous points.

First I’ve created a small class that represents a user. The class has the following standard props:

public String UserName { get; set; }
public String FirstName { get; set; }
public String LastName { get; set; }
public String Email { get; set; }

And a special prop that handles password hashing (never save a password as plain text in a database, always save a hash) (note: be sure to add a using directive for System.Security.Cryptography;)

public String Password
 {
 get
 {
 return passwordHashed;
 }
 set
 {
 UTF8Encoding encoder = new UTF8Encoding();
 MD5CryptoServiceProvider hasher = new MD5CryptoServiceProvider();
 passwordHashed = encoder.GetString(hasher.ComputeHash(encoder.GetBytes(value)));
 }
 }
 private string passwordHashed = "";

Now start up SQL Server Management Studio (Express/Basic) and navigate to your database, then select the folder programmability->stored procedures and right click to insert a new stored procedure. (Also make a user table if you haven’t got a table yet where you want to try this out on, I’m using a simple table with all the values as nvarchar(50)’s and the id as an int.)

Normally before you insert a user you first verify that the input is legit (a valid email address, strong enough passwords and a username exceeding some minimal length). That can all be handled client and server side using .Net’s validation controls.  Once you’ve done that you would query the database if the username and email address already exist. If not you would run another query (insert) to input the actual data.  Because when inserting a user you will always have to do the ‘check if exists’ query first, and inserting users might be a common task, there is room for improvement here.

I’ve done that using the following stored procedure:

Note: when multiple pages/threads call this sp it might suffer from race conditions, for more information about race conditions see the comments section. (Especially the first comment by Alister).

CREATE PROCEDURE InsertUniqueUser
@Username nvarchar(50),
@Password nvarchar(50),
@LastName nvarchar(50),
@FirstName nvarchar(50),
@Email nvarchar(50),
@UserID int OUTPUT
AS
IF NOT EXISTS(SELECT Username FROM Users WHERE Username=@Username OR Email=@Email)
 BEGIN
 INSERT INTO Users
 (Username, Password, LastName, FirstName, Email)
 VALUES (@Username, @Password, @LastName, @FirstName, @Email)
 SET @UserID = @@IDENTITY
 END
ELSE
 BEGIN
 SET @UserID = -1
 END

Be sure to execute this procedure to insert and compile the actual stored procedure.

As you can see this stored procedure has 5 inputs (Username etc..)  and one output: the UserID.  The syntax is pretty simple. First an internal (thus fast) query is performed to check if there exists a record that either has a username same as the input username or an email address same as the input email address. If this is not the case the actual work begins and we do a normal insert query. At the end of the query the UserID output value is set to either the identifier of the inserted record or –1 if there was no record inserted.

This procedure saves a round trip to the database, batches work, put’s extra constrains (and validation) on the data in the database and is safe (as we will see shortly after)

To use this fine stored procedure we will have to create a special form of a parameterized query in C# that looks like this:

int newID;
 SqlConnection connection = new SqlConnection(connectionString);
 SqlCommand command = new SqlCommand("InsertUniqueUser", connection);
 command.CommandType = System.Data.CommandType.StoredProcedure;
 command.Parameters.Add(new SqlParameter("@Username", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@Username"].Value = user.UserName;
 command.Parameters.Add(new SqlParameter("@Password", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@Password"].Value = user.Password;
 command.Parameters.Add(new SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@LastName"].Value = user.LastName;
 command.Parameters.Add(new SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@FirstName"].Value = user.FirstName;
 command.Parameters.Add(new SqlParameter("@Email", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@Email"].Value = user.Email;
 command.Parameters.Add(new SqlParameter("@UserID", System.Data.SqlDbType.Int, 4));
 command.Parameters["@UserID"].Direction = System.Data.ParameterDirection.Output;

try
 {
 connection.Open();
 command.ExecuteNonQuery();
 newID = (int)command.Parameters["@UserID"].Value;
 }
 catch (Exception ex)
 {
 //trace error
 string log = ex.Message;
 }
 finally
 {
 connection.Close();
 }
 return newID;

As you can see we are building a pritty normal stored procedure. be sure to System.Data.SqlDbType values for the SqlParameters. Also note that in the last line before the try we set the Direction of the “@UserID” parameter to Output. This way the stored procedure can store data in UserID.

If you build a small webpage/winforms app around this you will see that the newID returned is the ID value of the the newly inserted record in the database if there where no duplicates, or that the newID was –1 and that there haven’t been made new records.

Advertisements

10 Responses to “Fun with stored procedures: insert only if unique. (Transact-SQL/MS SQL)”

  1. Alister said

    Hi Roy,

    That will work as long as two threads don’t call the stored proc for the same user at the same time.

    See http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx for an explanation and a fix.

    • Hey Alister,

      You are absolutely right. Thanks for the url with extra information aswell! However this is a different race condition than you link to because I specifically don’t insert an ID (so you can’t get a primary key error as the examples shows). However as I said you are right that if 2 users insert the same username at the same time that there is a (very small) chance that they both inserted. This could be solved by a lock on the table. (however the chances are very very slimm that this will happen, and it can cause serious speed issues if you lock an entire table). It’s something that the utiliser of this stored procedure will have to balance out. But I should’ve informed my readers better.

  2. rend60 said

    Hi Royaleander,

    Would you please give a more detailed instruction on how to make a sample ASP page work? I have checked available topics but still cannot solve my problem although there are a lot of discussions.

    My problem is an old but basic one: inserting a user profile into SQL database table, making sure no same username inserted. If insert successful, present the user a confirmation message with the new username. If user name already exists, present the user a message to ask the user to pick another username. You know every membership site has to have this function.

    My true problem is not I cannot make my codes work. It is the codes are too long and in old fashion. I am just thinking there has to be a clean way to do it now in ASP.NET 3.5. We can insert and select records without using C# code by SQLDatasource separately. But how can we do it with one trip like yours? Or, any other simple way? Thanks!

    • Hey Rend60. ASP.Net2.0 / 3.5 in combination with SQL Server has a special function to insert and update members, it is called ‘profiles’ and you can find more on it at http://www.odetocode.com/articles/440.aspx

      For the rest, I can assure you that using C# code in ASP pages is not a bad practice at all, and unfortunately SQLDataSources can’t do everything for us. SO if the new profiles function is not powerfull enough you can just use this article as a guide. I cant really give you any more sourcecode since the last code block has all the C# code that you need to use this stored procedure with all its benefits.

      Good luck, and if you have any more questions feel free to ask away!

  3. rend60 said

    Hi Royalexander,

    Thanks for the information. I do not want to use the memebrship function since I am trying to learn some new ways to do the same thing without coding. I am not good at coding. So I am hard to figure out how to make your code work. If you can guide me (and rest of people asking the same question) in more detail, that will be great.

    By the way, the other day, I found a demo for how to do photo album by coding like yours. I was able to figure out a way to do the same thing without much coding in VS 2008. That is the motivation I think people will benefit from your original inspiring work. Thanks.

    • Hey again Rend60,

      I’m afraid I don’t know how to do this without using some code. I think what I have atm is really the least amount of code required to get this work, especially since we are using stored procedures. Unfortunately I don’t know how to bring this much easier, I have a sample webpage on my hdd, but I don’t think it’s going to make things much easier.

      If you want I can post that code here after the weekend (just leave a comment). If you think you need more help doing some small additions (of code) to your website, I can fix that for you quickly for a small fee. Just contact me via roy.triesscheijn@progpal.nl .

  4. rend60 said

    Hi Royalexander,

    Thanks a lot. I have figured out how to make your codes run. Basically, I have to build an User Class page, a ASPX page that have many function buttons and labels, and a Code behind page (I guess I can put the Class in the code behind page), and load the stored procedure into SQL database. After debugging all my typos, I could insert new records and retrieve the output parameter values.

    I think most valuable inspiration of your sample is how to catch the output values. There are hundreds of discussions but non of them got as clear as your codes. Actually, I have now been capable to do additional data manipulation within the stored procedure and output several parameter values to directly display on the ASPX page. I am in debt to other 2 people from whom I got inspired with other functions. Without finding back their names, I am afraid of showing their nice clean cut codes here. Your codes are good enough for people to catch up with the .net 3.5 techniques. If you can post more details that can still benefit other people. Thanks again.

    • Hey Rend60, great that it all works now! Thanks for all the compliments to my writings and code. I’ve got most of my inspiration from the book “Pro ASP.NET 3.5 in C# 2008” which really covers all that ASP has to offer, I would surely recommend it!

  5. Bilal said

    i have problem with insert stored procedure with a primary key …. plz help me

  6. Pragati said

    Hi Royalexander,
    My probelm is that I want to insert file name in database for that i wrote insert storeproc
    ALTER PROCEDURE [dbo].[Filerenamed_add]
    (

    @Filerenamed VARCHAR (50)
    )
    AS

    BEGIN
    INSERT INTO [RenamedFile] ( Filerenamed ) values ( @Filerenamed)
    END

    now I dont want same file again n again into table i use
    ALTER PROCEDURE [dbo].[Filerenamed_add]
    (

    @Filerenamed VARCHAR (50)
    )
    AS
    BEGIN
    IF NOT EXISTS(SELECT Filerenamed FROM RenamedFile WHERE Filerenamed = @Filerenamed)
    BEGIN
    INSERT INTO [RenamedFile] ( Filerenamed ) values ( @Filerenamed)
    END
    END

    GO

    but then in database it shows NULL value plz help I am new in SP

    Thanks P.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: