Bulk Load and SQL function defaults

Is it possible to include a field default in the schema file that represent
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql

built in function

How is it possible to show null if the field is empty?

i.e.
select
ID,
name
from
table1

it should show something like:
1, 'jo'
2, NULL
3, NULL
4, 'Jack'
...

Thanks

use nullif(column,'')

Code Snippet

Create Table #data (

[Id] int ,

[Name] Varchar(100)

);

Insert Into #data Values('1','jo');

Insert Into #data Values('2','');

Insert Into #data Values('3',' ');

Insert Into #data Values('3','');

Insert Into #data Values('4','Jack');

Select Id, nullif(name,'') From #data

|||

Try:

Code Snippet

select

ID,

nullif(ltrim(rtrim(name)), '') as [name]

from

table1

-- or

select

ID,

case when ltrim(rtrim(name)) = '' then NULL else [name] end as [name]

from

table1

AMB

|||

Kent,

I am not sure how COALESCE function will fit for this issue.

NULLIF function is the ANSI standard and supported by most of the RDBMS databases.

Building a date in SQL

Is there any sql method that takes 3 parameter like, day, month and year . And return me the date.

For example

function(10,3,2007) and it returns 10-03-2007

Thanks,

There is no such function as part of SQL2000 or SQL2005, however you could write your own function. You refer to the function returning 10-03-2007 as an example, however is it as string (char(10)) or as datetime? Also what datatype are the input parameters - integer?

|||

I have find the solution.

Thanks