locked
Stored Procedure to Insert Records into a junction table (Many to Many Relationship) RRS feed

  • Question

  • User-1352156089 posted

    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
    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.

    Would help me with this stored procedure?

    Thanks a lot

    Saturday, March 9, 2019 7:56 PM

Answers

  • User-893317190 posted

    Hi Claudio7810,

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 11, 2019 9:58 AM
  • User-893317190 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 18, 2019 7:39 AM

All replies

  • User-893317190 posted

    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

    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.

    Please refer to http://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/

    Best regards,

    Ackerly Xu

    Monday, March 11, 2019 2:43 AM
  • User-1352156089 posted

    I tried with both methods you suggested but the code throws always the same error again. 

    there must be something missing.

    Thank you

    Monday, March 11, 2019 9:52 AM
  • User-893317190 posted

    Hi Claudio7810,

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 11, 2019 9:58 AM
  • User-1352156089 posted

    Thanks a lot, you made my day!

    Monday, March 11, 2019 2:10 PM
  • User-1352156089 posted

    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

    Monday, March 11, 2019 4:59 PM
  • User-893317190 posted

    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

    Tuesday, March 12, 2019 1:28 AM
  • User-1352156089 posted

    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 

    Thursday, March 14, 2019 8:19 AM
  • User-893317190 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 18, 2019 7:39 AM
  • User-1352156089 posted
    Thank you Ackerly, this has pointed me tp the right direction and it worked like a charm.
    Monday, March 18, 2019 6:34 PM