October 17, 2010 at 10:45 pm
Hi created a store proc.In this 3 table insertion are done.
1.Consumer table
2.RoleConsumer table
3.RolePrivilege table
In the first insertion in Consumer table ConsumerID is generated and passed to the next insertion. In the RoleConsumer table insertion 2 or more ID can be Created. which i had done.
The problem is how to get 2 or more PK generated in the Second table to pass as FK for the 3rd RolePrivilege table Insertion. I'm not able to get the 2 or more ID generated in the second table to pass it to the third table ....
here is the Store Proc code with comments ..
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ConsumerInsert]
@vchConsumerName varchar(50)
,@vchAddress1 varchar(100)
,@vchAddress2 varchar(100)
,@vchEmail varchar(100)
,@vchContact varchar(20)
,@vchUserName varchar(50)
,@vchPassword varchar(50)
,@vchCurrencyCode varchar(3)
,@intModifiedByID int
,@bitIsActive bit
,@tintStatus tinyint OUTPUT
,@vchStatusMessage varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @vchStatusMessage = ''
BEGIN
IF EXISTS(
SELECT 1 FROM [dbo].[Consumer]
WHERE ConsumerName = @vchConsumerName
)
BEGIN
SET @tintStatus=1 -- 1 for Consumer name duplication
SET @vchStatusMessage = 'Consumer name already exists'
RETURN; -- Returns due to duplication
END
ELSE IF EXISTS(
SELECT 1 FROM [dbo].[Consumer]
WHERE UserName = @vchUserName
)
BEGIN
SET @tintStatus=2 -- 2 for User name duplication
SET @vchStatusMessage = 'Username already exists'
RETURN; -- Returns due to duplication
END
END
BEGIN TRY
BEGIN TRANSACTION
DECLARE@intConsumerID int
SELECT @intConsumerID = ISNULL(MAX(ConsumerID),0) + 1--Primary Key auto generated
FROM [dbo].[Consumer]
INSERT INTO [dbo].[Consumer] --first insertion only one ID generated whic is passed to the next table insertion below
(
ConsumerID--PK
,ConsumerName
,Address1
,Address2
,Contact
,UserName
,[Password]
,CurrencyCode
,ModifiedByID
,ModifiedDate
,IsActive
)
VALUES
(
@intConsumerID
,@vchConsumerName
,@vchAddress1
,@vchAddress2
,@vchEmail
,@vchContact
,@vchUserName
,@vchPassword
,@vchCurrencyCode
,@intModifiedByID
,GETDATE()
,@bitIsActive
)
BEGIN
DECLARE@intRoleConsumerIDint
SELECT @intRoleConsumerID =ISNULL(MAX(RoleConsumerID),0) --getting the maximum of Primary Key
FROM [dbo].[RoleConsumer]
INSERT INTO [dbo].[RoleConsumer] --for now two insertion are done so there is two PK Created.if there is three insertion then 3 PK created like that
( -- my problem how to get this 2 PK to be inserted in the next table as FK
RoleConsumerID --PK
,RoleName
,ConsumerID
,IsActive
,IsDefault
)
SELECT
(ROW_NUMBER() OVER(ORDER BY RoleID)) + @intRoleConsumerID
,RoleName
,@intConsumerID
,1 -- IsActive
,1 -- IsDefault
FROM [dbo].[tbl_OTM_WS_Role]
DECLARE @intRoleConsumerID1 int
SELECT @intRoleConsumerID1 = RoleConsumerID FROM [dbo].[RoleConsumer] --only able to get the last ID
INSERT INTO [dbo].[RolePrivilege] --Two PK(RoleConsumerID) created in the above insert are FK to this tableI
( --want both fk to be inserted but not able to get the both the ID only able to get the last
RoleConsumerID --FK --Inserted PK .In this table the PK is IDENTITY. Don't want to use Trigger due to some other
,UrlID --buisness logic
,[Read]
,Write
,Critical
)
SELECT
@intRoleConsumerID1
,UrlID
,[Read]
,Write
,Critical
FROM [dbo].[RolePrivilegeDefault]
END
COMMIT TRANSACTION
SET @tintStatus = 0 --success
RETURN;
END TRY
BEGIN CATCH
--SET @tintStatus = -1 -- error
-- Handling errors
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
END
DECLARE @Message VARCHAR(1000)
DECLARE @Severity int
DECLARE @State int
SET @Message = ERROR_MESSAGE()
SET @Severity = ERROR_SEVERITY()
SET @State = ERROR_STATE()
RAISERROR (@Message,@Severity,@State)
RETURN -1
END CATCH
END
please help me..
thank you
with regards
DD
October 17, 2010 at 11:07 pm
Look up the OUTPUT clause in SQL's Books Online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2010 at 8:13 am
thanks for the input ..
i implemented that and got the id and stored into the temp table .now i want to insert this ID's(2 or more ID's) into another table RolePrivilege which has RolePrivelegeID (IDENTITY) PK.
problem is how to insert the ID's stored in the temp table to this table in RoleConsumerID column which is FK.and other columns insertion from the other table
DECLARE@intRoleConsumerIDint
SELECT @intRoleConsumerID =ISNULL(MAX(RoleConsumerID),0) --getting the maximum of Primary Key
FROM [dbo].[tbl_OTM_WS_RoleConsumer]
DECLARE @RoleConsumerTemp table (ID int,Rolename varchar(50)) --temporary table
INSERT INTO [dbo].[tbl_OTM_WS_RoleConsumer]
(
RoleConsumerID
,RoleName
,ConsumerID
,IsActive
,IsDefault
)OUTPUT inserted.RoleConsumerID,inserted.RoleName INTO @RoleConsumerTemp
SELECT
(ROW_NUMBER() OVER(ORDER BY RoleID)) + @intRoleConsumerID
,RoleName
,@intConsumerID
,1 -- IsActive
,1 -- IsDefault
FROM [dbo].[tbl_OTM_WS_Role]
WHERE IsWsAdmin <> 1
--SELECT * FROM @RoleConsumerTemp
DECLARE @intRoleConsumerID1 int
SELECT @intRoleConsumerID1 = ID FROM @RoleConsumerTemp --try to insert but it takes only last ID i want both id
INSERT INTO [dbo].[tbl_OTM_WS_RolePrivilege]
(
RoleConsumerID
,UrlID
,[Read]
,Write
,Critical
)
SELECT
@intRoleConsumerID1
,UrlID
,[Read]
,Write
,Critical
FROM [dbo].[tbl_OTM_WS_RolePrivilegeDefault]
pls help
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply