Customized Error Message

Hi,
I have a table like this:
CREATE TABLE Table1 (
[ID] int NOT NULL Primary Key)
After inserting some records, obviously when I try to update all records to
a particular value, SQL server raises an error (number 2627) that indicates
the "Violation of PRIMARY KEY constraint" has happened.
What I need to do is to return an error message instead of SQL server's.
Suppose that I have this SP:
CREATE PROCEDURE UpdateTable1 AS
BEGIN TRAN
UPDATE table1 set id=1
IF @.@.Error = 2627
begin
print 'Duplicate Value'
raiserror('Duplicate Value',16,1)
rollback tran
end
else
begin
print 'Update was ok'
commit tran
end
GO
SQL server returns two error descriptions when I execute this SP: One from
its original messages and the other one from my raiserror statement.
I want to display my own error description to the client without writing
extra code for error handling in my client app(and also for centralizing my
own error descriptions those are returned instead of SQL server's error
messages).
Any help would be greatly appreciated.
Amin
See my reply in .programming. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message news:uy1qx39FEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table like this:
>
> CREATE TABLE Table1 (
> [ID] int NOT NULL Primary Key)
>
> After inserting some records, obviously when I try to update all records to
> a particular value, SQL server raises an error (number 2627) that indicates
> the "Violation of PRIMARY KEY constraint" has happened.
> What I need to do is to return an error message instead of SQL server's.
> Suppose that I have this SP:
>
> CREATE PROCEDURE UpdateTable1 AS
> BEGIN TRAN
> UPDATE table1 set id=1
> IF @.@.Error = 2627
> begin
> print 'Duplicate Value'
> raiserror('Duplicate Value',16,1)
> rollback tran
> end
> else
> begin
> print 'Update was ok'
> commit tran
> end
> GO
> SQL server returns two error descriptions when I execute this SP: One from
> its original messages and the other one from my raiserror statement.
> I want to display my own error description to the client without writing
> extra code for error handling in my client app(and also for centralizing my
> own error descriptions those are returned instead of SQL server's error
> messages).
> Any help would be greatly appreciated.
> Amin
>
>
>

Custom Primary Key

Not sure where to post this as it may be a form operation but here goes...

I want to generate a custom unique identifier for a table similar to the Northwind Customers database (ie ALFKI, ANTON, etc...) which can be used as a descriptive identifier as well.

For example, I have a Products table and I want to have my keys look like: CHR-0001-05.

"CHR" is my abbreviation for Chairs
"0001" is the auto-incrementing number (in this case the first record of the table)
"05" is the last 2 digits of the year recieved (2005)

Can somebody please point me in the right direction? I'm having problems finding resources on the web relating to this topic (prolly not using the correct keywords for my searches)

Cheers =)

You can use '+' operator to concatinate strings, here is a sample script:

if object_id('tbl_GPK','U') is not null
drop table tbl_GPK
go
Create table tbl_GPK (EName varchar(50),EDate smalldatetime)
go
insert into tbl_GPK select 'Peacock Margaret',GetDate()

if object_id('UDF_Gen_PK','FN') is not null
drop function UDF_Gen_PK
go
create function UDF_Gen_PK (@.S varchar(50))
returns varchar(60)
as
begin
declare @.outstring varchar(60)
declare @.i int
select @.i=1,@.outstring=''
while (@.i<=len(@.S))
begin
select @.outstring=@.outstring+substring(@.S,@.i,1)
set @.i=@.i+5
end
select @.outstring=@.outstring+'-'+convert(varchar(10),count(*)+1)
from tbl_GPK
return @.outstring
end

go
alter table tbl_GPK
add PK as (dbo.UDF_Gen_PK(EName)+'-'+convert(varchar(4),Year(EDate)))
go

select * from tbl_GPK

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
--
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
--
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
--
David Portas
SQL Server MVP
--