SQLTeam.com | Weblogs | Forums

UniqueIdentifier issue


#1

CREATE TABLE Bran (
BrandId uniqueIdentifier primary key default newid(),
BrandName nvarchar(254),
)

create table RemCont
(RemoteControlId nvarchar(254) not null primary key default newid(),
RemoteControlModelNumber nvarchar(254) unique,
RemoteControlType varchar(8) check( RemoteControlType in('Single','Uniersal')),
BrandID uniqueidentifier references Cpbran(brandid),
RemoteControlCode int identity(0001,1) unique
)
My question is if i use [BrandId uniqueIdentifier primary key default newid()] as foreigh key to another table
is there any way to pass the value to RemCont Table that instead of this key(BrandId).

if it is mandatory to pass this value with this key, then i could be a tedious job to type 32 bit data while i insert into RemCont table.

please suggest me a way.

if need to change the structure of table


#2

Type? You don't need to type: your application will take care of it.
Uniqueidentifier is by definition a surrogate key, so it must never be displayed to end users by the application itself.


#3

When you INSERT into [Bran] you can use an OUTPUT clause which will capture the GUID value assigned to the [BrandId] column, you can then use that for another INSERT into [RemCont]

Why is [RemoteControlId] defined with a datatype of nvarchar(254) if you are storing NewID() in it?

Does RemoteControlModelNumber need to be 254 characters wide?
Will it ever store Extended Characters?
If not use Varchar instead of NVarchar as it will preform better

Unless you need to have an ID that is unique across multiple servers I would recommend you use INT (e.g. IDENTITY) instead of UNQUEIDENTIFIER. If you must have a GUID then consider using NEWSEQUENTIALID() instead of NewID() because NewID() will cause huge fragmentation of indexes.

Did you mean "Universal"? If so the width needs to be 9, not 8.

FWIW we start the IDENTITY() in each table at a different number (e.g. 1,000 apart, or 1,000,000 apart if you like) This is so that when we are testing, with only small amounts of data, there is NO chance of the ID in one table matching an ID in another table - e.g. if we have accidentally got the wrong column name in a JOIN statement.


#4

hi Kristen,

thanks for your reply.

datatype for RemoteControlId is not nvarchar(254),

RemoteControlId uniqueIdentifier not null primary key default newid().

if you don't mind can you please update me the code with output clause.


#5

OK, that's a change from what you posted. I still recommend NEWSEQUENTIALID() though ...

Google will do a better job than me trying to guess what code you might need. Start here:

https://msdn.microsoft.com/en-us/library/ms177564.aspx


#6

yes but i need to provide a stored procedure to do the action.

in my stored proc BrandID uniqueidentifier references Cpbran(brandid) is the foregin key.
how can i implement in proc ?

CREATE TABLE CpBrand
(
BrandId uniqueIdentifier primary key default newid(),
BrandName nvarchar(254) unique,
)

insert into cpbrand( BrandName) values
('sony'),
('Panasonic'),
('Philips'),
('JVC'),
('Samsung')


create table CpRemoteControl
(
RemoteControlId uniqueIdentifier not null primary key default newid(),
RemoteControlModelNumber nvarchar(254) NOT NULL unique,
BrandID uniqueidentifier references Cpbrand(brandid),
UIDD INT IDENTITY(0001,1) ,
RemoteControlCode AS 'AA' + CAST(UIDD AS nVARCHAR(10)) PERSISTED
)

CREATE PROC SP_UID_CREATE
@RemoteControlId uniqueIdentifier,
@RemoteControlModelNumber nvarchar(254),
@BrandID uniqueidentifier,
@RemoteControlCode nvarchar(254) OUT
AS
BEGIN
INSERT INTO CpRemoteControl VALUES (@RemoteControlId,@RemoteControlModelNumber,@BrandID)
return @RemoteControlCode
END

declare @RemoteControlCode varchar(254)
EXEC SP_UID_CREATE 'ADDSS','{31a9e744-f024-4028-9541-0127cc4b7773}', @RemoteControlCode output
select @RemoteControlCode

i have created a proc [SP_UID_CREATE]
but when i execute the proc[SP_UID_CREATE] am getting error. here am stuck.

**(1 row(s) affected)**

Msg 8114, Level 16, State 5, Procedure SP_UID_CREATE, Line 53
Error converting data type varchar to uniqueidentifier.

please check with above code and test data.
give a action item which resolves the error in proc execution


#7

The parameters are in a different order to the definition which is

@RemoteControlId	uniqueIdentifier,
@RemoteControlModelNumber nvarchar(254),
@BrandID	uniqueidentifier,
@RemoteControlCode	nvarchar(254) OUT

I strongly recommend that you always label the parameters in the EXEC statement, then it doesn't matter what order they are in, e.g. (assuming I have guessed what they each do):

EXEC SP_UID_CREATE 
    @RemoteControlModelNumber = 'ADDSS',
    @BrandID= '{31a9e744-f024-4028-9541-0127cc4b7773}',
    @RemoteControlCode = @RemoteControlCode OUTPUT,
    @RemoteControlId = ???

Also you have only provided 3 parameters in your EXEC and the Sproc is defined with 4 parameters (and they are ALL required, no default values provided)


#8

@RemoteControlId uniqueIdentifier, auto generation default newid(), from below table.

create table CpRemoteControl
(
RemoteControlId uniqueIdentifier not null primary key default newid(),
RemoteControlModelNumber nvarchar(254) NOT NULL unique,
BrandID uniqueidentifier references Cpbrand(brandid),
UIDD INT IDENTITY(0001,1) ,
RemoteControlCode AS 'AA' + CAST(UIDD AS nVARCHAR(10)) PERSISTED
)


#9

The DDL for the Table has nothing to do with the problem.

You have this definition of your procedure:

CREATE PROC SP_UID_CREATE
@RemoteControlId	uniqueIdentifier,
@RemoteControlModelNumber nvarchar(254),
@BrandID	uniqueidentifier,
@RemoteControlCode	nvarchar(254) OUT
AS

There are no defaults, thus ALL FOUR PARAMETERS are required. Your EXEC:

EXEC SP_UID_CREATE 
    'ADDSS',
    '{31a9e744-f024-4028-9541-0127cc4b7773}', 
    @RemoteControlCode output

has only 3 parameters, and they are not named so will be assigned to parameters IN THE ORDER THAT THEY ARE WRITTEN

I recommend that you always specify the @Parameter name for all parameters to all Sprocs

Some reason you are ignoring my advice to use NEWSEQUENTIALID() ?


#10

surely ill implement NEWSEQUENTIALID(),
but i may concern is values are not insertted into CpRemoteControl
so at the moment my priority is to insert data into CpRemoteControl from CpBrand.

with your suggestion i have re-checked my PROC i updated now its executing perfectly..
below is the modified code.

i have deleted [RemoteControlId uniqueIdentifier not null primary key default newid()[ from SP_UID_CREATE

REATE PROC SP_UID_CREATE
@RemoteControlModelNumber nvarchar(254), -- i have deleted
@BrandID uniqueidentifier,
@RemoteControlCode nvarchar(254) OUT
AS
BEGIN
INSERT INTO CpRemoteControl (RemoteControlModelNumber,BrandID)VALUES (@RemoteControlModelNumber,@BrandID)
return @RemoteControlCode
END

declare @RemoteControlCode varchar(254)
EXEC SP_UID_CREATE 'test','{31a9e744-f024-4028-9541-0127cc4b7773}', @RemoteControlCode output

thanks.


#11

Still no parameter names ... you really should take head of the advice, as it will save you much time later with debugging, but its up to you ...

... waste of my time providing help though.


#12

surely ill consider your advice. am new to sql so am not able to fallow the syntax .

thanks for your advice


#13

here's a thing:

Your code is like this:

declare @RemoteControlCode varchar(254)
EXEC SP_UID_CREATE  
    'test',
    '{31a9e744-f024-4028-9541-0127cc4b7773}', 
    @RemoteControlCode output
GO

and the error message you got was "Error converting data type varchar to uniqueidentifier." - its very hard to decide what that refers to (it is actually that you are missing the first parameter, and all the other parameters are therefore "off by one")

If instead you name the parameters:

declare @RemoteControlCode varchar(254)
EXEC SP_UID_CREATE
	@RemoteControlModelNumber = 'test',
	@BrandID = '{31a9e744-f024-4028-9541-0127cc4b7773}',
	@RemoteControlCode = @RemoteControlCode output
GO

then the error message is "Procedure or function 'SP_UID_CREATE' expects parameter '@RemoteControlId', which was not supplied." - you'll be able to figure out what was wrong in a second !!

One other thing, do NOT name your procedures "SP_xxx" - the prefix "SP_" is used for system procedures and carries a performance penalty that SQL will check for the existence of the Procedure in the MASTER database (which it will waste time looking for and not finding)


#14

ok.....