sql server programming: I need to give my users the ability to change default values. I can
obviously use a hard-coded value here, but I cannot use a variable to re-add
the constraint. Any ideas?

ALTER TABLE Sales
ADD CONSTRAINT
DF_Sales_EstSubManDays
DEFAULT @EstSubManDays FOR EstSubManDays

A little experimentation, and found that this works:

EXEC ('alter table Sales ADD CONSTRAINT DF_Sales_EstSubManDays DEFAULT ' +
@EstSubManDays + ' FOR EstSubManDays')

[quoted text, click to view]

Certainly that will work but I suggest that it's unwise to allow end-users to
change the value of column defaults since those changes may impact others and
also break existing code. Don't you have a DBA with responsibility for change
control in your database? Or maybe your "user" is an "administrator" who
doesn't know how to do these things for him or her -self?!

If you need a user-configurable default, why not put the default values in
their own table? That way you could have a different default for each user or
other entity as required.

--
David Portas
SQL Server MVP
--

Thanks for the ideas David. I'll first note that indeed my initial solution
was to create a table with the default values. Frankly, I do not recall why
I rejected that approach. But I do see the safety factor involved in using
the separate table versus changing column defaults, so although I have the
code finished to implement my default changes, I may yet go with your
approach.

The reports that are generated out of this particular table require non-null
and non-zero values in several columns (cleaning the return value in code
would simply create inaccurate data). The situation is such that the default
values do indeed impact the organization system-wide -- but only to the
extent of showing what default values appear on a particular form. So
"different defaults for each user" would be irrelevant, although certainly I
can see that would be the best approach in most scenarios. And yes, none of
the users would have access directly to the server (MSDE).

In any event, I learned some interesting ways of manipulating the database
that I would not have if I had not gone the more difficult path.

Thanks again.

[quoted text, click to view]