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.
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.
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.
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
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):
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)
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
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")
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)