Thanks for everyone's responses.
I ended up taking it to the next level because there's really a double check that needs to be done to be 100% sure that you will be able to create the constraint. You need to make sure that the column in question doesn't already have a default value constraint and you have to make sure no constraint exists with the name you're going to use.
What I ended up with this...
CREATE FUNCTION dbo.FFG_FX_GET_CONSTRAINT_ID (@constraintName varchar(125),@tableName varchar(125),@columnName varchar(125)) RETURNS bit
AS
BEGIN
DECLARE @return int
SELECT @return = id FROM
(
SELECT constid as id FROM sysconstraints
WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName)
AND colid = (SELECT colid FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName) AND name = @columnName)
UNION
SELECT id FROM sysobjects WHERE name = @constraintName
) TBL
RETURN CONVERT(bit,isnull(@return,0))
END
GO
if not(exists(SELECT 'go' WHERE dbo.FFG_FX_GET_CONSTRAINT_ID('DF_AID_AGENCY_STAFF_CSR','AID_AGENCY_STAFF','CSR') = 1))
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINTDF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR
GO
It's not too hard to follow.
If either a constraint with that name is found or a constraint on that column is found then the function will return a 1.
If you are not concerned with one of these aspects, then you can just send in '' for either the constraint name or for the table and column name.
I had to use a function because I couldn't find the way to use a SProc within a If exists() statement......so if someone knows that trick then you could easily make this a SProc (which makes more sense given its use).