How to add a column with a default value to an existing table in SQL Server?

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES Example: ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records. Notes: … Read more

TSQL – Is it possible to define the sort order?

It’s incredibly clunky, but you can use a CASE statement for ordering: SELECT * FROM Blah ORDER BY CASE MyColumn WHEN ‘orange’ THEN 1 WHEN ‘apple’ THEN 2 WHEN ‘strawberry’ THEN 3 END Alternately, you can create a secondary table which contains the sort field and a sort order. TargetValue SortOrder orange 1 apple 2 … Read more

SQL update query syntax with inner join

The SET needs to come before the FROM\JOIN\WHERE portion of the query. UPDATE CE SET sJobNumber = AD.JobNumber FROM CostEntry CE INNER JOIN ActiveCostDetails As AD ON CE.lUniqueID = AD.UniqueID WHERE CE.SEmployeeCode=”002″ AND SubString(CostCentre, 1, 1) = sDepartmentCode AND substring(CostCentre, 3, 1) = sCategoryCode AND substring(CostCentre, 5, 2) = sOperationCode

What is the syntax to drop a Stored Procedure in SQL Server 2000?

Microsoft recommended using the object_id() function, like so: IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[YourProcedure]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) DROP PROCEDURE [dbo].[YourProcedure] GO . object_id() helps resolve owner conflicts. If you do SELECT name FROM sysobjects WHERE name=”my_procedure” , you may see many different procedures with the same name — all … Read more