sql server programming: [quoted text, click to view]


if COL_LENGTH('dbo.staff', 'setup devices') is NULL
begin;

ALTER TABLE dbo.staff ADD [setup devices] bit NULL ;

ALTER TABLE dbo.staff ADD CONSTRAINT [DF_staff_setup devices] DEFAULT 0
FOR [setup devices];

end;

GO

I would always advise against using spaces in column or object names.
Spaces make code harder to read and lead to more errors. Your question
is just one more example that proves the rule.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Hi All

I need to add some columns to a SQL server 2005 database

The following TSQL code returns an error 'incorrect syntax near 'setup
devices'

if COL_LENGTH('dbo.staff', '[setup devices]') is NULL

begin

ALTER TABLE dbo.staff ADD

[setup devices] bit NULL

ALTER TABLE dbo.staff ADD CONSTRAINT

DF_staff_[setup devices] DEFAULT 0 FOR [setup devices]

end

What am I doing wrong??

Regards

Steve

[quoted text, click to view]

Almost everything.

1) square brackets are proprietary; real SQL programmers use double
quotes a few times in their entire career.

2) You cannot mix regular and quoted identifier syntax together. But
you should not use quoted identifier syntax in the first place. It
tells me that you are formatting data in the back end in violation of
the principle of a tiered architecture.

3) [setup devices] BIT NULL shows us that: (1) You think a BIT has
three values. (2) You write with flags like you were in a 1950's punch
card system and not an RDBMS. In SQL we determine the state of an
entity from a predicate and not a flag.

You need a re-design of the schema, not some newsgroup kludges.

[quoted text, click to view]

Almost everything.

1) square brackets are proprietary; real SQL programmers use double
quotes a few times in their entire career.

2) You cannot mix regular and quoted identifier syntax together. But
you should not use quoted identifier syntax in the first place. It
tells me that you are formatting data in the back end in violation of
the principle of a tiered architecture.

3) [setup devices] BIT NULL shows us that: (1) You think a BIT has
three values. (2) You write with flags like you were in a 1950's punch
card system and not an RDBMS. In SQL we determine the state of an
entity from a predicate and not a flag.

You need a re-design of the schema, not some newsgroup kludges.

Hi Steve,
I ran your code on my computer and found that the issue came from the word
'DF_staff_[setup devices]'.
Please replace it with '[DF_staff_setup devices]'.

Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================