Database Reference
In-Depth Information
[State], [Country], [PWD])
VALUES (3, N'Scott Klein', N'sklein', N'',
N'Expert in software development. MVP SQL Server. Author. Speaker.',
N'Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)
SET IDENTITY_INSERT [dbo].[Users] OFF
/****** Object: StoredProcedure [dbo].[proc_CreateProfile]
Script Date: 03/31/2010 23:39:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_CreateProfile]
@uid [nvarchar](50),
@pwd [nvarchar](50),
@name [nvarchar](50),
@title [nvarchar](50),
@country [nvarchar](50),
@state [nvarchar](20),
@rowguid uniqueidentifier
WITH RECOMPILE, ENCRYPTION
AS
DECLARE @password varbinary(100)
SET @password = HASHBYTES('sha1', @pwd)
-- Make sure the UID is not already taken...
IF (Exists(SELECT TOP 1 * FROM Users WHERE NTUserName = @uid))
BEGIN
RAISERROR(N'0x001 - User ID already in use', 16, 1)
END
ELSE
BEGIN
INSERT INTO Users
(Name, NTUserName, Domain, Intro, Title, State, Country, PWD, rowguid)
VALUES
(@name, @uid, '', '', @title, @state, @country, @password, @rowguid)
END
GO
Notice that the script enables several options, such as
ANSI_NULL
and
ANSI_PADDING
. Then, the script
creates the Users table. This table has an IDENTITY column as well as a rowguid column that uses the
uniqueidentifier database. The rowguid column also has a default on it, which uses the
NEWSEQUENTIALID()
function to automatically generate new GUIDs. This table is created on the
PRIMARY
file group, followed by the setting of several table options via the
WITH
clause.
Further down in the script, several stored procedures are created, one of which is shown in the
preceding code snippet. proc_CreateProfile is a standard stored procedure that accepts several input
parameters and uses the
WITH
option to specify procedure options: in this case,
RECOMPILE
(to indicate
that the database engine doesn't need to cache a plan for this procedure and to compile the procedure
at runtime) and
ENCRYPTION
(indicating that SQL Server converts the text of this stored procedure to an
obfuscated format).
