need help concatenating a string.....thanks 3

[aldi]

Hello all,

I'm trying to create a custom auto-generated sequence number for a primary key.

The failing statement is this:

RETURN 'C' + right('0000' + CONVERT(varchar(10), @id))


I'm expecting the it to return sequential numbers as followed:

C0001
C0002
C0003
.
.

but I get the error: right function requires 2 argument(s).

I tried this:

RETURN 'C' + CONVERT(varchar(10), @id)

but I get:

C1
C2
C3
.
.

Also tried this:

RETURN 'C' + right('0000' + CONVERT(varchar(10), @id),4)

but I get: C0000, C0000,....

Can anyone please help with this?

Thanks in advance!

Aldi

 

[VVVA]

Got a paren in the wrong place I think.

RETURN 'C' + right('0000' + CONVERT(varchar(10), @id))

Should be

RETURN 'C' + right('0000' + CONVERT(varchar(10), @id),4)

Is that right?

 

[SQLDenis]

declare @id int
select @id = 1

select 'C' + right('0000' + CONVERT(varchar(10), @id),4)

BTW return can only RETURN integers, you need to use an OUTPUT parameter in that case

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Might I suggest that you use a 'regular' integer identity value for your primary key. This will make inserts and joins faster. You can then create a computed column for this value. Your performance will be better and your database will thank you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If you are interested, I'll be willing to show you how to set this up. Just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aldi]

VVVA

Works great....thanks!

SQLDenis/George thanks to you too;

This is what I have now:

Fields:

[dbID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NULL,
[CustomerNumber] AS ([dbo].[CustomerNumber]([dbID]))

Function:

ALTER FUNCTION [dbo].[CustomerNumber] (@id int)
RETURNS char(5) AS
BEGIN
RETURN 'C' + right('0000' + CONVERT(varchar(10), @id),4)
END

Which is giving me what I want (C0001,C0002,...) but I want to make sure you guys agree.

btw...George, thanks for offering extra help

One more thing that I want to do is to switch the pk from dbid to CustomerNumber instead, but it gives the following error:

'Customer' table
- Unable to create index 'PK__Customer__7C8480AE'.
Cannot define PRIMARY KEY constraint on nullable column in table 'Customer'.
Could not create constraint. See previous errors.

Is this error caused because CustomerNumber is a computed column?

As I typed it just occurred to me that perhaps the table must be empty before making the change....maybe? I'll try anyway

Thanks a LOT!!!

 

[aldi]

I cleared the table but no luck.

I get the following error:

Msg 8183, Level 16, State 1, Line 4
Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.

When tried to edit the table as follow:

CREATE TABLE [dbo].[Customer](
[dbID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NULL,
[CustomerNumber] AS ([dbo].[CustomerNumber]([dbID])) NOT NULL,
CONSTRAINT [PK__Customer__CustNum] PRIMARY KEY CLUSTERED
(
[CustomerNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO