Discrete Information

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

Tags: MS SQL, OUTPUT

This entry was posted on Tuesday, May 26th, 2009 at 9:22 pm and is filed under Programming, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.