If Exists Statement In Stored Procedure

Hello all!

Newbie question:

There appears to be something wrong with this syntax in SQL Server 2000:

CREATE PROCEDURE spAddNewUser

@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT

AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

I can't get past the last "if exists" statement, without getting a syntax error when checking syntax. I get "ERROR: Incorrect Syntax near ')'.

I'm sure it's a very simple mistake...

Thanks in advance for any help :-)I think the IF statement is expecting some more code.

if i add the code

begin
print 'yes'
end

after your code then i don't get any parse errors.

What extra code do you want to put in, as your stored procedure does not do anything at the mo :confused:|||I don't know if the code you have shown is what you have, but if it is you are missing the brackets around the paramter list.

CREATE PROCEDURE spAddNewUser
(
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
)
AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

Also don't forget to use BEGIN and END if you need to run a block of code when the IF statment is true.|||Try this way:

CREATE PROCEDURE spAddNewUser
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
AS
IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)
select 'exists!'
ELSE
select 'not exists!'|||all of you are correct...I guess it was just waiting for more info (what happens AFTER the IF EXISTS statement).

I just went ahead and completed the code and it was fine.

<blush>

:-)

IF ELSE not compiling

I can't seem to figure out why this SP would not compile. Your assistance i
s
much appreciated.
CREATE PROCEDURE sp_getPubId
@.name varchar, @.pubCode varchar, @.pubId smallint Output
AS
DECLARE maxPubId smallint
if (exists
(select publication_id from publication where code =@.pubCode))
begin
@.maxPubId=(select publication_id from publication where code =@.pubCode)
@.pubId=@.maxPubId
end
else begin
@.maxPubId=select max(publication_id) from publication
insert into publication(publication_id, name, code, creation_date,
update_date, last_user_id, status, transmit_app, application_type,
flags)values(@.maxPubId, @.name, @.pubCode, getDate(), getDate(), 13, 1, 1, 1,0
)
@.pubId=@.maxPubId+1
end
return
--
bicHi
Just try it this way:
SET @.maxPubId=(select publication_id from publication where code =@.pubCode)
SET @.pubId=@.maxPubId
.
.
.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"bic" wrote:

> I can't seem to figure out why this SP would not compile. Your assistance
is
> much appreciated.
> CREATE PROCEDURE sp_getPubId
> @.name varchar, @.pubCode varchar, @.pubId smallint Output
> AS
> DECLARE maxPubId smallint
> if (exists
> (select publication_id from publication where code =@.pubCode))
> begin
> @.maxPubId=(select publication_id from publication where code =@.pubCode)
> @.pubId=@.maxPubId
> end
> else begin
> @.maxPubId=select max(publication_id) from publication
> insert into publication(publication_id, name, code, creation_date,
> update_date, last_user_id, status, transmit_app, application_type,
> flags)values(@.maxPubId, @.name, @.pubCode, getDate(), getDate(), 13, 1, 1, 1
,0)
> @.pubId=@.maxPubId+1
> end
> return
> --
> bic|||Thanks so much. You've solved a problem which plagued me for better part of
today. Thanks again.
--
bic
"bic" wrote:

> I can't seem to figure out why this SP would not compile. Your assistance
is
> much appreciated.
> CREATE PROCEDURE sp_getPubId
> @.name varchar, @.pubCode varchar, @.pubId smallint Output
> AS
> DECLARE maxPubId smallint
> if (exists
> (select publication_id from publication where code =@.pubCode))
> begin
> @.maxPubId=(select publication_id from publication where code =@.pubCode)
> @.pubId=@.maxPubId
> end
> else begin
> @.maxPubId=select max(publication_id) from publication
> insert into publication(publication_id, name, code, creation_date,
> update_date, last_user_id, status, transmit_app, application_type,
> flags)values(@.maxPubId, @.name, @.pubCode, getDate(), getDate(), 13, 1, 1, 1
,0)
> @.pubId=@.maxPubId+1
> end
> return
> --
> bic

IDENTITY on a BitMap column?

Consider the following table
CREATE TABLE Attributes( id int identity(1,1),
name varchar(100),
mask bigint)
Now, attribute mask will be a bit mask of the attribute name. Consider the
data:
id name mask
-- -- --
1 foo 1
2 bar 2
3 mann 4
4 frau 8
5 kein 16
6 alles 32
How do I put a trigger on this table, or something so that I do not have to
worry about the mask when I insert data? Inserting 1 record at a time would
be no problem, just get the MAX of mask and double it. But how to handle
two or more inserts at a time? Should I use an instead-of-trigger?
Sorry about the poor english.
Dieter"Dieter Katzenland" <deiter@.rrtc.com> wrote in
news:#e8DYffjDHA.2964@.tk2msftngp13.phx.gbl:
> How do I put a trigger on this table, or something so that I do not
> have to worry about the mask when I insert data? Inserting 1 record
> at a time would be no problem, just get the MAX of mask and double it.
> But how to handle two or more inserts at a time? Should I use an
> instead-of-trigger?
hi,
in this case it would be enough to let the mask empty on inserting and then
use an AFTER INSERT Trigger for calculating the mask.
--
best regards
Peter Koen
--
MCAD, CAI/R, CAI/S, CASE/RS, CAT/RS
http://www.kema.at|||Assuming that your multi-row INSERT originates from a table or query:
CREATE TABLE foo (name VARCHAR(10) PRIMARY KEY)
INSERT INTO foo VALUES ('Alpha')
INSERT INTO foo VALUES ('Beta')
INSERT INTO Attributes (id, name, mask)
SELECT COUNT(*)+
(SELECT MAX(id) FROM attributes),
A.name,
POWER(2,COUNT(*))*
(SELECT MAX(mask) FROM attributes)
FROM foo AS A
JOIN foo AS B
ON A.name >= B.name
GROUP BY A.name
--
David Portas
--
Please reply only to the newsgroup
--

Identity keys in a One-to-Zero-or-Many relationship

Considering the following:
CREATE TABLE CustomerTypes
(
PKCustomerType INT IDENTITY (1,1) NOT NULL,
CustomerTypeDesc VARCHAR(30)
)
GO
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
CREATE TABLE Customers
(
PKCustomer INT IDENTITY (1,1) NOT NULL,
CustomerID VARCHAR(20),
FKCustomerType INT
)
GO
INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
INSERT Customers Values ('CUST002', 3)
INSERT Customers Values ('CUST003', 2)
How should I handle a case where I don't want to specify a customer
type for whatever reason? Perhaps it is unknown or not applicable in
some cases. I could add a 'None or N/A' record in the CustomerTypes
table for that purpose, but when validating the data in an INSERT
stored procedure, for example, I would like to have a value to use as
default in the case of a missing of invalid field value being passed
for FKCustomerType. If it weren't an identity field in the
CustomerTypes table, I could just create a CustomerType record with a
PKCustomerType value of 0 and always use that value in such cases. With
it being an identity field, however, I can't count on a specific value
for that purpose. It seems I have two options:
1) Create the CustomerTypes table without PKCustomerType being an
identity field...insert the 'None or N/A' record, assigning it a
PKCustomerType value of, say, 0...alter the table to make
PKCustomerType and identity field (1,1)
2) Merely use NULL in Customers where CustomerType isn't specified.
Is there a better way? If not, any insight on why one approach would be
better than the other? I'm leaning toward option 2 at this point.> 2) Merely use NULL in Customers where CustomerType isn't specified.
If CustomerType is not specified because it is unknown, then this may be a
valid approach.
However, you may want to give users the option of saying Unknown, and have
it be a value in the CustomerTypes table. Then if you later want to
differentiate between unknown and unspecified, you can add an option.
There is no reason to hard-code the default value in the parameter to the
stored procedure. Have the application generate its dropdown from the
CustomerTypes table, and you are done.
A|||>> Considering the following: <<
Let's fix what you posted so that it follows the most basic RDBMS
design principles. For example, why do you have no keys? Why did you
think that IDENTITY is every used? Why did you use PK- prefixes in
violation of ISO-11179 rules?
CREATE TABLE CustomerTypes
(customer_type INTEGER NOT NULL PRIMARY KEY,
customer_type_desc VARCHAR(30) NOT NULL);
I see from the use of IDENTITY that you did not bother with designing
an encoding scheme. You might want to learn how to do that
CREATE TABLE Customers
(customer_id VARCHAR(20) NOT NULL PRIMARY KEY,
(customer_type INTEGER NOT NULL
REFERENCES CustomerTypes (customer_type)
ON UPDATE CASCADE,
. );
VARCHAR(20) is a bit long, but by the definition of an identifier, this
has to be your key. Frankly, I would look for a DUNS number or some
indusrty standard code.
Stop putting prefixes that tell you **how** a data element is used in a
table. The name of a data element is supposed to tell you **what** it
is.
Then you need codes for those situations if they are logically
different. For example, the ICD codes for disease have '000.000' which
means "undiagnosed" and '999.999' which means "we did all the test and
still don't know!" -- very different kinds of missing data! VERY
IMPORTANT to distinguish them!! Matter of life and death, in fact.
] value being passed for FKCustomerType [sic]. <<
Now we are getting to your REAL problem. Let's get back to the basics
of an RDBMS. Rows are not records; fields are not columns; tables are
not files; there is no sequential access or ordering in an RDBMS, so
faking a record number or the lines on a piece of paper with a
proprietary IDENTITY property is dead wrong. You do not have the right
mindset, and all you are going to get on a Newsgroup is a few kludges
to help you fake it for a long period of time before the collapse.
a specific value for that purpose. <<
Surprise! Surprise! Surprise! See how non-relational, non-verifiable,
non-portable proprietary extensions screw up things?
Yes; do it right or kludge it :)
<<
Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
file system.
Maybe; do you need to know anything about the missing values? Or just
that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
SMARTIES or DATA & DATABASES and then read the chapters on scales &
measurements, and how to design encoding schemes. The research pattern
is simple:
1) Look for industry standards (Google it!)
2) Look for company standards (see the accounting department for help)
3) In the remaining 5% of the cases where you have to invent something,
pick a type of encoding and follow the rules for good design. My guess
in this example is a hierachy or vector code because customers break
down into tax/tax exempt, domestic/foreign and so forth within
retailers and wholesalers.|||> design principles. For example, why do you have no keys? Why did you
> think that IDENTITY is every used? Why did you use PK- prefixes in
Why do you think SURROGATE KEYS are never used? It would appear you are one
of the only people in the industry who do not use them.
Perhaps its an implementation experience thing compared to theory.

> Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
> file system.
Your bias and misunderstanding of how IDENTITY works is embarrasing - READ
THE MANUAL!

> that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
> SMARTIES or DATA & DATABASES and then read the chapters on scales &
Only if you want theory, for practical advice get a SQL Server specific
book.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151601604.581696.66800@.75g2000cwc.googlegroups.com...
> Let's fix what you posted so that it follows the most basic RDBMS
> design principles. For example, why do you have no keys? Why did you
> think that IDENTITY is every used? Why did you use PK- prefixes in
> violation of ISO-11179 rules?
> CREATE TABLE CustomerTypes
> (customer_type INTEGER NOT NULL PRIMARY KEY,
> customer_type_desc VARCHAR(30) NOT NULL);
> I see from the use of IDENTITY that you did not bother with designing
> an encoding scheme. You might want to learn how to do that
> CREATE TABLE Customers
> (customer_id VARCHAR(20) NOT NULL PRIMARY KEY,
> (customer_type INTEGER NOT NULL
> REFERENCES CustomerTypes (customer_type)
> ON UPDATE CASCADE,
> .. );
> VARCHAR(20) is a bit long, but by the definition of an identifier, this
> has to be your key. Frankly, I would look for a DUNS number or some
> indusrty standard code.
> Stop putting prefixes that tell you **how** a data element is used in a
> table. The name of a data element is supposed to tell you **what** it
> is.
>
> Then you need codes for those situations if they are logically
> different. For example, the ICD codes for disease have '000.000' which
> means "undiagnosed" and '999.999' which means "we did all the test and
> still don't know!" -- very different kinds of missing data! VERY
> IMPORTANT to distinguish them!! Matter of life and death, in fact.
>
> Now we are getting to your REAL problem. Let's get back to the basics
> of an RDBMS. Rows are not records; fields are not columns; tables are
> not files; there is no sequential access or ordering in an RDBMS, so
> faking a record number or the lines on a piece of paper with a
> proprietary IDENTITY property is dead wrong. You do not have the right
> mindset, and all you are going to get on a Newsgroup is a few kludges
> to help you fake it for a long period of time before the collapse.
>
> Surprise! Surprise! Surprise! See how non-relational, non-verifiable,
> non-portable proprietary extensions screw up things?
>
> Yes; do it right or kludge it :)
>
> Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
> file system.
>
> Maybe; do you need to know anything about the missing values? Or just
> that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
> SMARTIES or DATA & DATABASES and then read the chapters on scales &
> measurements, and how to design encoding schemes. The research pattern
> is simple:
> 1) Look for industry standards (Google it!)
> 2) Look for company standards (see the accounting department for help)
> 3) In the remaining 5% of the cases where you have to invent something,
> pick a type of encoding and follow the rules for good design. My guess
> in this example is a hierachy or vector code because customers break
> down into tax/tax exempt, domestic/foreign and so forth within
> retailers and wholesalers.
>|||> How should I handle a case where I don't want to specify a customer
> type for whatever reason? Perhaps it is unknown or not applicable in
It should be NULL because its not specified, if you want to give it a value
you really ought to have a CustomerType of 'Not Specified'.

> CREATE TABLE CustomerTypes
> (
> PKCustomerType INT IDENTITY (1,1) NOT NULL,
> CustomerTypeDesc VARCHAR(30)
> )
> GO
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
> CREATE TABLE Customers
> (
> PKCustomer INT IDENTITY (1,1) NOT NULL,
> CustomerID VARCHAR(20),
> FKCustomerType INT
> )
> GO
> INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
> INSERT Customers Values ('CUST002', 3)
> INSERT Customers Values ('CUST003', 2)
You probably need to have a slight rethink on the constraints....
CREATE TABLE CustomerTypes
(
id INT IDENTITY (1,1) NOT NULL constraint sk_customertypes unique
clustered,
CustomerTypeDesc VARCHAR(30) constraint pk_customertype primary key
nonclustered
)
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
CREATE TABLE Customers
(
id INT IDENTITY (1,1) NOT NULL constraint sk_customer unique
clustered,
CustomerID VARCHAR(20) not null constraint pk_customer primary key
nonclustered,
CustomerType_id INT NULL references CustomerTypes( id )
)
INSERT Customers (CustomerID, CustomerType_id) Values ('CUST001', 1)
INSERT Customers (CustomerID, CustomerType_id) Values ('CUST003', NULL)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1151594340.887702.165300@.b68g2000cwa.googlegroups.com...
> Considering the following:
> CREATE TABLE CustomerTypes
> (
> PKCustomerType INT IDENTITY (1,1) NOT NULL,
> CustomerTypeDesc VARCHAR(30)
> )
> GO
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
> CREATE TABLE Customers
> (
> PKCustomer INT IDENTITY (1,1) NOT NULL,
> CustomerID VARCHAR(20),
> FKCustomerType INT
> )
> GO
> INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
> INSERT Customers Values ('CUST002', 3)
> INSERT Customers Values ('CUST003', 2)
> How should I handle a case where I don't want to specify a customer
> type for whatever reason? Perhaps it is unknown or not applicable in
> some cases. I could add a 'None or N/A' record in the CustomerTypes
> table for that purpose, but when validating the data in an INSERT
> stored procedure, for example, I would like to have a value to use as
> default in the case of a missing of invalid field value being passed
> for FKCustomerType. If it weren't an identity field in the
> CustomerTypes table, I could just create a CustomerType record with a
> PKCustomerType value of 0 and always use that value in such cases. With
> it being an identity field, however, I can't count on a specific value
> for that purpose. It seems I have two options:
> 1) Create the CustomerTypes table without PKCustomerType being an
> identity field...insert the 'None or N/A' record, assigning it a
> PKCustomerType value of, say, 0...alter the table to make
> PKCustomerType and identity field (1,1)
> 2) Merely use NULL in Customers where CustomerType isn't specified.
> Is there a better way? If not, any insight on why one approach would be
> better than the other? I'm leaning toward option 2 at this point.
>|||Why not use a referential integrity constraint?
Unknown and Inapplicable are distinct reasons attributed for missing data.
And therefore, if your business demands that distinction, it makes sense to
use separate values to represent them in a table.
Anith|||Just to add, inapplicable attributes are best addressed using an entity
super-type/sub-type relationship. However, commonly people use to kludge
them with NULLs as an easy workaround.
Anith|||"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1151594340.887702.165300@.b68g2000cwa.googlegroups.com...
> Considering the following:
> CREATE TABLE CustomerTypes
> (
> PKCustomerType INT IDENTITY (1,1) NOT NULL,
> CustomerTypeDesc VARCHAR(30)
> )
> GO
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale')
> INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate')
> CREATE TABLE Customers
> (
> PKCustomer INT IDENTITY (1,1) NOT NULL,
> CustomerID VARCHAR(20),
> FKCustomerType INT
> )
> GO
> INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1)
> INSERT Customers Values ('CUST002', 3)
> INSERT Customers Values ('CUST003', 2)
> How should I handle a case where I don't want to specify a customer
> type for whatever reason? Perhaps it is unknown or not applicable in
> some cases. I could add a 'None or N/A' record in the CustomerTypes
> table for that purpose, but when validating the data in an INSERT
> stored procedure, for example, I would like to have a value to use as
> default in the case of a missing of invalid field value being passed
> for FKCustomerType. If it weren't an identity field in the
> CustomerTypes table, I could just create a CustomerType record with a
> PKCustomerType value of 0 and always use that value in such cases. With
> it being an identity field, however, I can't count on a specific value
> for that purpose. It seems I have two options:
> 1) Create the CustomerTypes table without PKCustomerType being an
> identity field...insert the 'None or N/A' record, assigning it a
> PKCustomerType value of, say, 0...alter the table to make
> PKCustomerType and identity field (1,1)
> 2) Merely use NULL in Customers where CustomerType isn't specified.
> Is there a better way? If not, any insight on why one approach would be
> better than the other? I'm leaning toward option 2 at this point.
>
If you add some keys to the CustomerTypes table you will be able to identify
the "Unknown" value by its logical key instead of the surrogate.
The "Inapplicable" case would probably be better handled by decomposing the
Customers table.
CREATE TABLE CustomerTypes
(
PKCustomerType INT IDENTITY (1,1) NOT NULL
CONSTRAINT PK_CustomerTypes PRIMARY KEY,
CustomerTypeDesc VARCHAR(30) NOT NULL
CONSTRAINT AK1_CustomerTypes UNIQUE
);
GO
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail');
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale');
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate');
INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Unknown');
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
--|||Good gravy! I'll try to respond to your individual points without
taking too much offense to your abrasive tone...
--CELKO-- wrote:
> Let's fix what you posted so that it follows the most basic RDBMS
> design principles.
Really, don't trouble yourself. It was a simple example I threw
together to try and illustrate my question. Nothing more. I wasn't
shooting for theoretical precision. I didn't (and still don't, as I
will explain in a moment) think it that necessary in this case.

> For example, why do you have no keys? Why did you
> think that IDENTITY is every used? Why did you use PK- prefixes in
> violation of ISO-11179 rules?
> CREATE TABLE CustomerTypes
> (customer_type INTEGER NOT NULL PRIMARY KEY,
> customer_type_desc VARCHAR(30) NOT NULL);
> I see from the use of IDENTITY that you did not bother with designing
> an encoding scheme. You might want to learn how to do that
> CREATE TABLE Customers
> (customer_id VARCHAR(20) NOT NULL PRIMARY KEY,
> (customer_type INTEGER NOT NULL
> REFERENCES CustomerTypes (customer_type)
> ON UPDATE CASCADE,
> .. );
> VARCHAR(20) is a bit long, but by the definition of an identifier, this
> has to be your key. Frankly, I would look for a DUNS number or some
> indusrty standard code.
It is what it is. If my particular (hypothetical, I might add) business
process requires the use of specific proprietary customer types, then
why complicate it?

> Stop putting prefixes that tell you **how** a data element is used in a
> table. The name of a data element is supposed to tell you **what** it
> is.
I really don't see the significance of the distinction. The fact that
the name of the field starts with PK *does* tell me what it is. I
really think this one is a matter of personal preference.

> Then you need codes for those situations if they are logically
> different. For example, the ICD codes for disease have '000.000' which
> means "undiagnosed" and '999.999' which means "we did all the test and
> still don't know!" -- very different kinds of missing data! VERY
> IMPORTANT to distinguish them!! Matter of life and death, in fact.
A bit dramatic, but I hear what you're saying. However, my question
really doesn't have anything to do with what values are available to
the user, but how to handle it when the user doesn't feel inclined to
specify a fact that is not strictly required. On the application's data
entry screen, the user may only need to worry about some of the
available fields. There is no reason to force them to touch every one
when not all are relevant in every case. My question is, how to best
handle that foreign key [column] value in that case.

ic] value being passed for FKCustomerType [sic]. <<
> Now we are getting to your REAL problem. Let's get back to the basics
> of an RDBMS. Rows are not records; fields are not columns; tables are
> not files; there is no sequential access or ordering in an RDBMS, so
> faking a record number or the lines on a piece of paper with a
> proprietary IDENTITY property is dead wrong. You do not have the right
> mindset, and all you are going to get on a Newsgroup is a few kludges
> to help you fake it for a long period of time before the collapse.
Yeah, I'm guilty of referring to them in the very manner that makes a
certain type of person grit their teeth. I think that since we both
know exactly what I mean, then it really isn't an issue worth allowing
to cloud the matter. Wouldn't you agree?

on a specific value for that purpose. <<
> Surprise! Surprise! Surprise! See how non-relational, non-verifiable,
> non-portable proprietary extensions screw up things?
>
Hence my original question. Again, I really don't understand why it has
to be this difficult. How would *you* go about it, if forcing the user
to touch every field on the data entry form is *not* acceptable?

> Yes; do it right or kludge it :)
>
1) <<
> Almost! Stop using IDENTITY. This is an RDBMS and not a sequential
> file system.
There is nothing sequential about the requirements I've established
here. I don't care if the key is sequentially numbered, random
alpha-numeric or caveman hieroglyphics. All I require is that it be
guaranteed to be unique and generated automatically.

> Maybe; do you need to know anything about the missing values? Or just
> that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR
> SMARTIES or DATA & DATABASES and then read the chapters on scales &
> measurements, and how to design encoding schemes. The research pattern
> is simple:
> 1) Look for industry standards (Google it!)
> 2) Look for company standards (see the accounting department for help)
> 3) In the remaining 5% of the cases where you have to invent something,
> pick a type of encoding and follow the rules for good design. My guess
> in this example is a hierachy or vector code because customers break
> down into tax/tax exempt, domestic/foreign and so forth within
> retailers and wholesalers.
Honestly, that just seemed like a whole lot of unnecessary nit-picking
and tangent traversal than was really required here. I can understand
if you feel in your infinite wisdom that it is better to suggest to me
where else I might direct my search to find the answer than give it to
me directly, but to put so much effort into avoiding answering my
question while at the same time trying to educate me (or, arguably more
accurate, indoctrinate me according to your own personal opinions) in
related regards does make me wonder who's best interest you have at
heart here.|||Anith Sen wrote:
> Why not use a referential integrity constraint?
> Unknown and Inapplicable are distinct reasons attributed for missing data.
> And therefore, if your business demands that distinction, it makes sense t
o
> use separate values to represent them in a table.
>
That would be my typical thinking as well, but RI doesn't work if the
value is not required unless a default constraint is specified. With an
identity key, the default value for the foreign key column cannot be
specified in the table definition.
On second thought, a better illustration of my question would be a name
suffix (Jr., Sr. III, etc.). A person won't necessarily *have* a suffix
in their name, yet forcing the user to select "None" from a list is
perhaps a bit cumbersome from a UI standpoint. If that suffix column in
the Customers table is a foreign key referencing the primary key column
in a reference table, and that primary key column is an identity type
key, then it would seem that the only workable foreign key value would
be NULL.

IDENTITY Column!

A table named Products1 containing 20 records has the following
columns:
PID int IDENTITY (1,1)
PCode varchar(50)
PName varchar(50)
PDesc varchar(500)
Price money
Qty int
I created another table named Products2 whose design is exactly the
same as the design of the table named Products1 i.e. the PID column in
Products2 is also an IDENTITY(1,1) column. I issued the following query
to populate Products2:
---
SET IDENTITY_INSERT Products2 ON
GO
INSERT INTO Products2 (PID,PCode,PName,PDesc,Price,Qty)
SELECT * FROM Products1
---
The above query, when executed in QA, populates Products2 with the
records existing in Products1 but if the above query is executed again,
Products2 again gets populated with 20 records existing in Products1
which is OK but the PID values of the 2nd set of 20 records remain the
same as that of the first set of 20 records i.e. there are duplicate
PID values but IDENTITY columns are supposed to identify each row
uniquely which the IDENTITY column PID doesn't do here! So does this
mean that PID no longer remains an IDENTITY column? Shouldn't SQL
Server generated an error when the INSERT query was executed for the
second time?
Had the PID column been a PRIMARY KEY column, executing the INSERT
query in QA two (or more) times rightly generates a PRIMARY KEY
Constraint error but why doesn't the same happen with an IDENTITY
column?
Thanks,
ArpanIDENTITY doesn't guarantee uniquness--especially when you're using
IDENTITY_INSERT, something that should only be used when combining tables or
databases (once in a blue moon). You must either have a primary key or
unique constraint on the IDENTITY column.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123968967.850494.94880@.o13g2000cwo.googlegroups.com...
> A table named Products1 containing 20 records has the following
> columns:
> PID int IDENTITY (1,1)
> PCode varchar(50)
> PName varchar(50)
> PDesc varchar(500)
> Price money
> Qty int
> I created another table named Products2 whose design is exactly the
> same as the design of the table named Products1 i.e. the PID column in
> Products2 is also an IDENTITY(1,1) column. I issued the following query
> to populate Products2:
> ---
> SET IDENTITY_INSERT Products2 ON
> GO
> INSERT INTO Products2 (PID,PCode,PName,PDesc,Price,Qty)
> SELECT * FROM Products1
> ---
> The above query, when executed in QA, populates Products2 with the
> records existing in Products1 but if the above query is executed again,
> Products2 again gets populated with 20 records existing in Products1
> which is OK but the PID values of the 2nd set of 20 records remain the
> same as that of the first set of 20 records i.e. there are duplicate
> PID values but IDENTITY columns are supposed to identify each row
> uniquely which the IDENTITY column PID doesn't do here! So does this
> mean that PID no longer remains an IDENTITY column? Shouldn't SQL
> Server generated an error when the INSERT query was executed for the
> second time?
> Had the PID column been a PRIMARY KEY column, executing the INSERT
> query in QA two (or more) times rightly generates a PRIMARY KEY
> Constraint error but why doesn't the same happen with an IDENTITY
> column?
> Thanks,
> Arpan
>|||Thanks, Brian, for your input but BOL states that IDENTITY columns
contain system-generated values that uniquely identify each row within
a table. So is BOL wrong?
Thanks once again,
Regards,
Arpan|||You got everything wrong. Please read a book on RDBMS.
Rows are not records; IDENTITY cannot ever be a relational key. I find
it amazing that you have a product code that changes size and can be
CHAR(50) and NULL, etc. If you knew what you were doing and had posted
DDl, would look like this?
CREATE TABLE Products
(product_id CHAR(13) NOT NULL PRIMARY KEY -- upc' idustry standard
product_name CHAR(20) NOT NULL,
product_descr VARCHAR (250) NOT NULL,
product_price DECIMAL (8,2) NOT NULL
CHECK (product_price > 0.00),
qty_on_hand INTEGER NOT NULL
CHECK (qty_on_hand > 0),
product_status INTEGER DEFAULT 1 NOT NULL
CHECK (product_status IN (1,2) );
One of the basic ideas of RDBMS is that each table is a set of the same
kind of entities. If two tables have the same structure then they
model the same entity. What you probably need is a status code to show
the LOGICAL difference between a table 1 and table 2 products. Surely,
you are not just shifting rows from table to table, to mimic a punch
card or magnetic tape file system!
This is a dangerous option used with a non-relational, proiprietary
feature that should not have been there anyway. You have gone from bad
to worse.
Stop what you are doing. Read a book or two. Start over.|||Well, Celko, I guess you have dug in too deep in the example I have
shown or you are trying to read too much in between the lines. This is
just a hypothetical scenario....definitely not a practical one. Of
course, having 2 such tables just doesn't make any sense. I wanted to
get my doubt on IDENTITY clarified which is why I cited those 2 tables.
Maybe I could have given a better example but couldn't think of
anything else within the stipulated time of 2-3 minutes I was given to
frame my query (I am on my friend's computer)!!
So please take it easy :-)
Thanks,
Regards,
Arpan|||BOL is not wrong. IDENTITY_INSERT bypasses the normal behavior of IDENTITY.
If you don't use IDENTITY_INSERT, then absent a catastrophic system failure,
the generated IDENTITY values will always be unique. That's why it's use
should be limited. There are instances when you want to specify the
identity values, for example, when you're combining databases or tables. A
further limitation is that IDENTITY_INSERT can only be on for one table at a
time per session. It is a tool for a database administrator, to be used
only when absolutely necessary.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123972518.924767.159220@.g47g2000cwa.googlegroups.com...
> Thanks, Brian, for your input but BOL states that IDENTITY columns
> contain system-generated values that uniquely identify each row within
> a table. So is BOL wrong?
> Thanks once again,
> Regards,
> Arpan
>|||Thank you very much, Brian, for helping me clarify my doubt.
Regards,
Arpan

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
BevoI don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:
> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> > Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> >
> > The application is trying to run this insert into the table:
> >
> > insert into X (MYID, MYVAR) values (null, 'test').
> >
> > This error is being returned:
> >
> > Cannot insert explicit value for identity column in table 'X' when
> > IDENTITY_INSERT is set to OFF.
> >
> >
> > I cannot modify the application code insert. Is there a way to configure
> the
> > IDENTITY to replace the null with the IDENTITY value and not error on the
> > attempt.
> >
> > I know a trigger can be written to do this, but I want to know if
> something
> > can be configured in the IDENTITY or TABLE.
> >
> > Thanks!!
> > Bevo
>
>|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.
> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.
> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.
> I cannot modify the application code insert.
!!' Even when it won't work? That seems a bizzare and unreasonable.
--
David Portas
SQL Server MVP
--|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
--
David Portas
SQL Server MVP
--|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
--
David Portas
SQL Server MVP
--

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
Bevo
I don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo
|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>
|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!? Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
|||Why you ask. Well, the application is currently supported on MySQL and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limits
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
BevoI don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying t
o
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!' Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
--|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
--|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
--|||Why you ask. Well, the application is currently supported on mysql and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limit
s
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>