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