Discrete Information

As seen in earlier posts, a very useful clause in SQL Server 2005 and beyond is OUTPUT. Here is an example within an UPDATE statement.

Here’s our table structure:

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
BEGIN
  DROP TABLE [users];
END;

CREATE TABLE [users] (
  [user_id] INT PRIMARY KEY IDENTITY(1,1),
  username VARCHAR(16),
  [password] VARCHAR(16),
  email VARCHAR(128)
);

Here is a changeUserName procedure to update a user’s username, using an OUTPUT clause:

CREATE PROC changeUserName
  @user_ip varchar(15),
  @password varchar(16),
  @username_old varchar(16),
  @username_new varchar(16)
AS

  SET NOCOUNT ON;

  CREATE TABLE #updatedUser(
    user_id INT,
    username_old VARCHAR(16),
    username_new VARCHAR(16));

  UPDATE [users]
    SET [username] = @username_new
  OUTPUT DELETED.user_id, DELETED.username, INSERTED.username
    INTO #updatedUser(user_id, username_old, username_new)
  WHERE [users].username = @username_old
    AND CAST([users].[password] AS VARBINARY(16))
      = CAST(@password AS VARBINARY(16));

  SET NOCOUNT OFF;

  SELECT user_id, username_old, username_new
    FROM #updatedUser;

  DROP TABLE #updatedUser;

Here is an example of that procedure in action:

EXEC [addUser] @user_ip = '192.168.1.2',
  @username = 'john',
  @password = 'john!password',
  @email = 'john@yahoo.com';

user_id
-----------
1

EXEC [changeUserName]
  @user_ip = '192.168.1.1',
  @username_old = 'john',
  @username_new = 'john_new',
  @password='john!password';

user_id     old_username     new_username
------- ---------------- ----------------
1           john         john_new

One of the more useful clauses available in SQL Server 2005 and beyond is OUTPUT, but it is rarely seen in use. Here is a quick example of how to use it within an INSERT statement.

Let’s setup our table structure first:

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
BEGIN
  DROP TABLE [users];
END;

CREATE TABLE [users] (
  [user_id] INT PRIMARY KEY IDENTITY(1,1),
  username VARCHAR(16),
  [password] VARCHAR(16),
  email VARCHAR(128)
);

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'login_activity')
BEGIN
  DROP TABLE [login_activity];
END;

CREATE TABLE [login_activity] (
  user_id INT,
  user_ip VARCHAR(15),
  login_username VARCHAR(16),
  login_success BIT,
  login_time DATETIME
);

Let’s assume we want an addUser procedure to create a user, log in said user, and return the new user’s user_id. Here is how we could do that with the OUTPUT clause:

CREATE PROC addUser
  @user_ip VARCHAR(15),
  @username VARCHAR(16),
  @password VARCHAR(16),
  @email VARCHAR(128)
AS

SET NOCOUNT ON;

CREATE TABLE #new_user(user_id INT);

INSERT INTO users (username, [password], email)
OUTPUT inserted.[user_id] INTO #new_user(user_id)
VALUES (@username, @password, @email);

INSERT INTO login_activity(user_id, login_ip, login_username,
  login_success, login_time)
SELECT user_id, @username, @user_ip, 1, CURRENT_TIMESTAMP
FROM #new_user;

SET NOCOUNT OFF;

SELECT user_id
FROM new_user;

EXEC [addUser] @user_ip = '192.168.1.1',
  @username = 'bob',
  @password = 'bob!password',
  @email = 'bob@gmail.com';

user_id
-----------
1

EXEC [addUser] @user_ip = '192.168.1.2',
  @username = 'john',
  @password = 'john!password',
  @email = 'john@yahoo.com';

user_id
-----------
2