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