In page 2 I am feeding a checkboxlist with both the RoomName text and values and I have a Textbox that I use to insert the RateTypeName into the RateType table.
Before clicking on the Create RatePlanName button I need to select the RoomName from the checkboxlist previously created and what I would like to understand is how to add the RoomTypeId, RateTypeId
into the junction table named RateRoomTypeCombination.
This the Stored Procedure that I have done so far:
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spAddRateTypeAndRoomtareCombination_UsingExists]
(
@RatePlanName nvarchar(50),
@OpenFrom date,
@OpenTo date,
@Active bit,
@RateTypeId int,
@RoomTypeId int
)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
SELECT
NULL
FROM
dbo.RateType WITH (UPDLOCK)
WHERE
RatePlanName= @RatePlanName
)
BEGIN
SELECT @Result = -1
END
ELSE
BEGIN
INSERT INTO
dbo.RateType
(
RatePlanName,
OpenFrom, OpenTo, Active
)
VALUES
(
@RatePlanName,
@OpenFrom, @OpenTo, @Active
)
/****** return the last identity value inserted into an identity column ******/
SET @RateTypeId = SCOPE_IDENTITY()
INSERT INTO dbo.RateRoomCombination
(
RateTypeId, RoomTypeId
)
VALUES
(
@RateTypeId, @RoomTypeId
)
SELECT @Result = @@ERROR
END
IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result
GO
However, when I execute the above stored procedure:
USE [MyDB]
GO
EXEC dbo.spAddRateTypeAndRoomtareCombination_UsingExists @RatePlanName = 'Standard', @OpenFrom='2019-03-10T00:00:00', @OpenTo='2019-04-10T00:00:00', @Active=True, @RoomTypeId='5'
The error is: Msg 201, Level 16, State 4, Procedure dbo.spAddRateTypeAndRoomtareCombination_UsingExists, Line 0 [Batch Start Line 2] Procedure or function 'spAddRateTypeAndRoomtareCombination_UsingExists' expects parameter '@RateTypeId', which was not supplied.
From the exception , your stored procedure needs a parameter named @RateTypeId and when you execute the procedure you haven't provide the @RateTypeId.
From your stored procedure,it seems that you could only get the RateTypeId in your stored procedure
SET @RateTypeId = SCOPE_IDENTITY()
So I suggest you could remove the parameter in your stored procedure.
alter PROCEDURE [dbo].[spAddRateTypeAndRoomtareCombination_UsingExists]
(
@RatePlanName nvarchar(50),
@OpenFrom date,
@OpenTo date,
@Active bit,
-- @RateTypeId int, remove the parameter or write @RateTypeId int output
@RoomTypeId int
)
AS
Or if your you want to get teh RateTypeId , you could declare it as output parameter.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
If you remove the parameter, -- @RateTypeId int, you should first declare it and then set it.
declare @RateTypeid int
SET @RateTypeId = SCOPE_IDENTITY()
Best regards,
Ackerly Xu
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
it appears that my stored procedure is incomplete.
In fact, while it works like a charm if passing only 1 RoomTypeId (from checkbox list), it doesnt work if more than 1 room types are selected:
Msg 8144, Level 16, State 2, Procedure dbo.spAddRateTypeRoomTypeCombination, Line 0 [Batch Start Line 2] Procedure or function spAddRateTypeRoomTypeCombination has too many arguments specified.
There is still something missing here that I cannot understand.
From your stored procedure, you have @RatePlanName nvarchar(50), @OpenFrom date, @OpenTo date, @Active bit, @RoomTypeId int 5 parameters, (if you have removed @RateTypeId int output) , so every time you should only pass the 5 parameter to the stored procedure.
You could not pass fewer or more parameter than it needs.
What your stored procedure does is that if the RatePlanName have existed in table RateType, does nothing (using rollback).
Else, it will insert into a new record to the table RateType with @RatePlanName, @OpenFrom, @OpenTo, @Active you have passed to the stored procedure.
And then it will get the newly generated RateTypeId of the record and insert into the table RateRoomCombination with RateTypeId and the RoomTypeId you have passed.
Is this what you want? If it isn't , I think you should redesign the stored procedure.
In addition, you could not pass RoomTypeId more than once every time you call the stored procedure, if you have many roomTypeId , you should call the stored procedure many times.
Best regards,
Ackerly Xu
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
I think I will need to redesign my stored procedure because what you described is true 100%. I would like to add in the RoomTypeCombination table all the RoomTypeIds selected from a checkbox list and add for each RoomType added the last added RateType in
one insert.
I have been told I can do that with table valued parameters so I ll need to figure out how to make it work.
Do you have any suggestion about how to accomplish the above?
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
143 Points
447 Posts
Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 09, 2019 07:56 PM|Claudio7810|LINK
Hi All,
I am quite new to SQL and I am wondering how to insert records into a junction table named RateRoomTypeCombination.
Here is what I am trying to achieve:
I have 3 tables:
RoomType (RoomTypeId, RoomName)
RateType (RateTypeId, RateTypeName)
RateRoomTypeCombination (RateRoomTypeCombinationId, RoomTypeId, RateTypeId)
I am adding the RoomName in page 1.
In page 2 I am feeding a checkboxlist with both the RoomName text and values and I have a Textbox that I use to insert the RateTypeName into the RateType table.
Before clicking on the Create RatePlanName button I need to select the RoomName from the checkboxlist previously created and what I would like to understand is how to add the RoomTypeId, RateTypeId into the junction table named RateRoomTypeCombination.
This the Stored Procedure that I have done so far:
USE [MyDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spAddRateTypeAndRoomtareCombination_UsingExists] ( @RatePlanName nvarchar(50), @OpenFrom date, @OpenTo date, @Active bit, @RateTypeId int, @RoomTypeId int ) AS DECLARE @Result int BEGIN TRANSACTION IF EXISTS ( SELECT NULL FROM dbo.RateType WITH (UPDLOCK) WHERE RatePlanName= @RatePlanName ) BEGIN SELECT @Result = -1 END ELSE BEGIN INSERT INTO dbo.RateType ( RatePlanName, OpenFrom, OpenTo, Active ) VALUES ( @RatePlanName, @OpenFrom, @OpenTo, @Active ) /****** return the last identity value inserted into an identity column ******/ SET @RateTypeId = SCOPE_IDENTITY() INSERT INTO dbo.RateRoomCombination ( RateTypeId, RoomTypeId ) VALUES ( @RateTypeId, @RoomTypeId ) SELECT @Result = @@ERROR END IF @Result <> 0 BEGIN ROLLBACK END ELSE BEGIN COMMIT END RETURN @Result GOHowever, when I execute the above stored procedure:
The error is: Msg 201, Level 16, State 4, Procedure dbo.spAddRateTypeAndRoomtareCombination_UsingExists, Line 0 [Batch Start Line 2]
Procedure or function 'spAddRateTypeAndRoomtareCombination_UsingExists' expects parameter '@RateTypeId', which was not supplied.
Would help me with this stored procedure?
Thanks a lot
Contributor
3500 Points
1300 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 11, 2019 02:43 AM|Ackerly Xu|LINK
Hi Claudio7810,
From the exception , your stored procedure needs a parameter named @RateTypeId and when you execute the procedure you haven't provide the @RateTypeId.
From your stored procedure,it seems that you could only get the RateTypeId in your stored procedure
So I suggest you could remove the parameter in your stored procedure.
alter PROCEDURE [dbo].[spAddRateTypeAndRoomtareCombination_UsingExists] ( @RatePlanName nvarchar(50), @OpenFrom date, @OpenTo date, @Active bit, -- @RateTypeId int, remove the parameter or write @RateTypeId int output @RoomTypeId int ) ASOr if your you want to get teh RateTypeId , you could declare it as output parameter.
Please refer to http://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
143 Points
447 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 11, 2019 09:52 AM|Claudio7810|LINK
I tried with both methods you suggested but the code throws always the same error again.
there must be something missing.
Thank you
Contributor
3500 Points
1300 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 11, 2019 09:58 AM|Ackerly Xu|LINK
Hi Claudio7810,
If you remove the parameter, -- @RateTypeId int, you should first declare it and then set it.
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
143 Points
447 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 11, 2019 02:10 PM|Claudio7810|LINK
Thanks a lot, you made my day!
Member
143 Points
447 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 11, 2019 04:59 PM|Claudio7810|LINK
Sorry Ackerly,
it appears that my stored procedure is incomplete.
In fact, while it works like a charm if passing only 1 RoomTypeId (from checkbox list), it doesnt work if more than 1 room types are selected:
Msg 8144, Level 16, State 2, Procedure dbo.spAddRateTypeRoomTypeCombination, Line 0 [Batch Start Line 2]
Procedure or function spAddRateTypeRoomTypeCombination has too many arguments specified.
There is still something missing here that I cannot understand.
Thanks for your help
Contributor
3500 Points
1300 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 12, 2019 01:28 AM|Ackerly Xu|LINK
Hi Claudio7810,
From your stored procedure, you have @RatePlanName nvarchar(50), @OpenFrom date, @OpenTo date, @Active bit, @RoomTypeId int 5 parameters, (if you have removed @RateTypeId int output) , so every time you should only pass the 5 parameter to the stored procedure. You could not pass fewer or more parameter than it needs.
What your stored procedure does is that if the RatePlanName have existed in table RateType, does nothing (using rollback).
Else, it will insert into a new record to the table RateType with @RatePlanName, @OpenFrom, @OpenTo, @Active you have passed to the stored procedure.
And then it will get the newly generated RateTypeId of the record and insert into the table RateRoomCombination with RateTypeId and the RoomTypeId you have passed.
Is this what you want? If it isn't , I think you should redesign the stored procedure.
In addition, you could not pass RoomTypeId more than once every time you call the stored procedure, if you have many roomTypeId , you should call the stored procedure many times.
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
143 Points
447 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 14, 2019 08:19 AM|Claudio7810|LINK
Hi Ackerly,
thank you again for your feedback.
I think I will need to redesign my stored procedure because what you described is true 100%. I would like to add in the RoomTypeCombination table all the RoomTypeIds selected from a checkbox list and add for each RoomType added the last added RateType in one insert.
I have been told I can do that with table valued parameters so I ll need to figure out how to make it work.
Do you have any suggestion about how to accomplish the above?
Thanks
Contributor
3500 Points
1300 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 18, 2019 07:39 AM|Ackerly Xu|LINK
Hi Claudio7810,
Maybe you could try to create a new type in your stored procedure so that it could accept table variable.
Please refer to https://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net to learn how to pass table variable to stored procedure in ado.net.
Then you could try insert into yourtargettable select * from your tablevariable to insert all your data in your tablevariable into yourtargettable.
https://www.dofactory.com/sql/insert-into
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
143 Points
447 Posts
Re: Stored Procedure to Insert Records into a junction table (Many to Many Relationship)
Mar 18, 2019 06:34 PM|Claudio7810|LINK