A friend of mine sent me an email the other day asking about default values in SQL Server. I realized that I’ve had to think about this a few times over the years and I’ve been asked about it more than once, too.
Setup We need a table first, right? We’ll also want a few sample rows in there.``` CREATE TABLE Employees ( emp_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, emp_name varchar(50) NULL); GO
Sometimes you just have to be able to dynamic search criteria in a stored procedure. What’s going to be the best way to accomplish this? You could use dynamic SQL and run the risk that you won’t have compiled execution plans, syntax highlighting, or readable SQL code. That’s not really an elegant solution. Let’s get started with a User table: CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, [EmailAddress] [varchar](255) NOT NULL, [Username] [varchar](50) NOT NULL, [Password] [varchar](256) NOT NULL, [PasswordHint1] [int] NOT NULL, [PasswordHint1Question] [varchar](255) NOT NULL, [PasswordHint1Answer] [varchar](50) NOT NULL, [PasswordHint2] [int] NOT NULL, [PasswordHint2Question] [varchar](255) NOT NULL, [PasswordHint2Answer] [varchar](50) NOT NULL, [FailedLoginAttempts] [smallint] NOT NULL CONSTRAINT[DF_User_FailedLoginAttempts] DEFAULT ((0)), [AccessLevelId] [int] NOT NULL, [ClientID] [int] NOT NULL, [SiteID] [int] NOT NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserId] ASC ) ); The goal here is to be able to SELECT a user by username for a combination of ClientId and SiteId, but at least one must be present.