How to build a SP which INSERTS a record and RETURNS the PK-(new())
-
Hello All,
I want to create a Stored Proc to INSERT a record into table 1,
returns the value of guProfileID (the value created via newID()) form the INSERT and use it to populate guProfile field for an INSERT of record in Table2
Thanks in advance for your input !!!! Yorgs
Table 1
CREATE TABLE [dbo].[tblProfile] (
[guProfileID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[iProfileType] [int] NOT NULL ,
[txtProfileID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[txtCreatorCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtCreatedDate] [datetime] NOT NULL ,
[txtLastUpdaterCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtLastUpdated] [datetime] NULL ,
[curCreditLimit] [money] NULL ,
[guPrimaryLanguageID] [uniqueidentifier] NULL ,
[timestamp_column] [timestamp] NULL
) ON [PRIMARY]
Table 2
CREATE TABLE [dbo].[tblNameInfo] (
[guNameInfoID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[guProfileID] [uniqueidentifier] NULL ,
[txtNamePrefix] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtNameFirst] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[txtNameMiddle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtNameSur] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[txtNameSuffix] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtNameTitle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtNameInit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtNameOrdered] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[timestamp_column] [timestamp] NULL
) ON [PRIMARY]
GO
-
It's simple. Assign the new GUI ID into a variable first and use it in both inserts.
declare @Gui as uniqueidentifier
set @Gui = newid()
insert into table1 (guProfileID,......)
values( @GUI,......)
if @@rowcount = 1
insert into table2....
Remember this works only if you insert 1 record at a time as you have explained.
-
Thank you Bimal I will give it a try
-
Or ...
If you have an identity column in teh table do the insert and then use
SET guid = @@IDENTITY
Once again this only work when inserting oe record at a time.
-
>>Or ...
If you have an identity column in teh table do the insert and then use
SET guid = @@IDENTITY
----------------------
SCOPE_IDENTITY() is more accurate than @@IDENTITY. Check out books online for more info.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply