Updating primary key value in sql server via stored procedure

Transact-SQL
1

Hello great guys in the house. I need your support and advice on this.

  1. I have a cust table which is populated via store procedure from web application API services.
  2. For old and existing clients, a customer-key which is the primary key is generated but for new Clients, no customer-Key is created.
  3. The issue and my question is, is it possible to assign a dummy customer-key for new Clients and change or modify the value at later point.
  4. In table cust, there are dependancies of about 5 tables or more.

Below is the same table. The customerID is auto generated and is not the key. The primary key is Customerkey.

CREATE TABLE [cust].[Cust](
[CustomerID] [int] NOT NULL,
[CustomerKey] varchar NOT NULL,
[CompanyName] varchar NULL,
[CreatedBy] varchar NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedOn] [datetime] NULL
)

Below is the store proce:

Create proc [dbo].[usp_Insert_Cust]

(
@CustomerKey AS NVARCHAR(25)
,@CompanyName AS NVARCHAR(150)
,@CreatedOn AS DATETIME
,@CreatedBy AS NVARCHAR(50)

)
AS
BEGIN
DECLARE @ClientId AS INT
INSERT INTO TBLCustomers
(
CustomerKey
,CompanyName
,CreatedOn
,CreatedBy
)
VALUES
(
@CustomerKey
,@CompanyName
,@CreatedOn
,@CreatedBy

)
SET @ClientId = Scope_identity()
SELECT @ClientId as NewClientId
END

CREATE TABLE [cust].[Cust](
[CustomerID] [int] NOT NULL,
[CustomerKey] varchar NOT NULL,
[CompanyName] varchar NULL,
[CreatedBy] varchar NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedOn] [datetime] NULL
)

Any idea how I would approach this without blowing-up the whole database.

thanks
Boyo

2

This I don't get:

Why not? Surely that is basic business information? and...what is a typical CustomerKey in your app?

THis:

looks odd. First of all, SCOPE_IDENTITY() only has meaning when there is an identity column in the target table. Your DDL doesn'd show that. Second, the SELECT will only return that variable to the caller, not change the table, Is that what you want?

3

Many thanks.
The typical customerkey looks this - YPE002. I am not sure why new customers are not assigned any a new customerkey upon creation. But, my question is, if they are assigned a dummy customerkey at the initial stage, can I then change that value to the real customerkey and if yes, how do I do that considering the other dependancies of the table.

thanks
Boyo

4

There are also problems with your table and code...

First - you really need to declare the size of varchar/char/nvarchar/nchar columns. Relying on a 'default' size is going to cause you lots of problems later.

CREATE TABLE [cust].[Cust](
[CustomerID] [int] NOT NULL,
[CustomerKey] varchar**(25)** NOT NULL,
[CompanyName] varchar**(150)** NULL,
[CreatedBy] varchar**(50)** NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedOn] [datetime] NULL
)

Next - you have the procedure declared using NVARCHAR - but the table defined as VARCHAR. This will fail at some point and will be nearly impossible to find why...

The fact that your CustomerKey is defined as the PRIMARY KEY - I would NOT recommend changing it at a later point. Changing the primary key would require modifying the value in all related tables which could be quite cumbersome to accomplish - it would depend on how many related tables, the relationships and how much data actually has to be changed.

Based on your table structure - it looks like CustomerID is an identity column that is also (probably) the clustering key. You then have CustomerKey defined as your primary key which isn't going to allow NULL values - so you have to define a value.

As to your question - can you generate a value for that column...yes it can be generated, but how it is generated will depend on the business requirements. How are the old/existing values defined? Are they an arbitrary generated value - or do they combine different elements like location, year, month, etc...?

Once this value is generated it should never be changed. If this value is assigned by some external source then it should be supplied when the customer record is created...and not some 'dummy' value assigned and then changed at a later point.

Of course - if CustomerKey is just some arbitrary value then there is no reason to even have the column since you already have an arbitrary value being generated as CustomerID and could just use that as your primary key. But...you would still need to be able to uniquely identify the customer using other columns and data.

5

For new customers, you could put this in CustomerKey:

CustomerID1
CustomerID2
CustomerID3
etc.