Default Constraints (call: User-Defined Function or proc)

Hi
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

Default constraints

Hello

I have about 40 tables where I need to increase the size of the admission number field from smallint to Int . The field used the tables' primary key or part of the key. I am a programmer, but fairly new to SQL Server. I have written some scripts to remove the defaults and primary key constraints off of this field in each table, do the field resize and then put the constraints back on. The scripts get the names and settings of the constraints from system tables before the constraints are dropped, so that they can be reapplied after the field size change.

Is this the best way to do it? Or should I be looking at a DTS package?

I would be grateful for your advice

Shirley

To me (unless you have a database design tool), this sounds like a sufficient way to do it. It is never perfectly easy or anything, but you can get most everything you need to generate a script using the system tables, so I would do that. Sounds messy of course, since you also have to deal with foreign key constraints, but if it is just 40 tables, that probably isn't too bad. (I assume you will make ths size plenty big for the next sixty years this time :)

|||

Thanks very much for that. Yes, Integer will definitely be a big enough field size for the forseeable future for our admission number!

Shirley

DEFAULT constraint not working

I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this

TRUNCATE TABLE NDRS_Call_Data_Table

INSERT INTO NDRS_Call_Data_Table(<column-list>)
SELECT <column-list> FROM NDRS_Call_Data_View

The view is a complex join of several other tables. When I run the proc, I get the error:

"Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."

BUT, if I simply do this from Query Analyzer.....

INSERT INTO NDRS_Calls_Data_Table(data_indicator)
VALUES('Z')

it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)

Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.|||Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks|||Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.|||Originally posted by cvandemaele
Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.

Well, forget about this last post. Just getting a cup of coffee and WHAM problem solved.|||Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT ... SELECT syntax|||To solve the problems of nulls in your view, you can use isnull(a, b) where a is the selected column and b is the default value for that column.

example:

select isnull (title, 'No title') as title, au_lname
from authors a left join titleauthor ta on a.au_id = ta.au_id left join titles t on t.title_id = ta.title_id|||True.

Undortunately, when using INSERT ... SELECT you can't say
SELECT ..., ISNULL(column, DEFAULT), ...

the way you can use the DEFAULT keyword like this...

INSERT
VALUES (x,y,z,DEFAULT,p,q,r)

You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.|||you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:

declare @.value int
select @.value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
from syscomments m,syscolumns c,sysobjects o
where c.id=object_id('dbo.your_table')
and c.name=('your_column_name')
and o.type='d'
and m.id=c.cdefault
and m.id=o.id

DECLARE in SQL CE

Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?

Matt

You do not have to specify a value for fields with primary key/identity constraints. See this help topic for more info:

http://msdn2.microsoft.com/en-us/library/ms174633.aspx

|||Sorry, I didn't mean primary key constraints. I meant, how do I insert into Table1 if it has a Column which is a Foreign Key referencing Table2?

Matt
|||If you are referring to IDENTITY columns, you can query "SELECT @.@.IDENTITY" (on the same open connection where you just did the INSERT), and use this value as the foeign key in the next INSERT.