Wednesday, February 27, 2013 6:48 PM
I want to insert multiple records in a database and the stored proc below is inserting only one? How should I modify it?
create PROCEDURE [dbo].sp_MethodInsertUpdate ( @Use_RID int, @Method_RID int, @Group_RID int, @Use_Name varchar(50), @Use_Description varchar(500), @Use_Help varchar(500), @Use_IsRequired bit, @Use_CanDelete bit, @DateModified datetime, @Entity_Type varchar(10) = 'M', @Use_IsVisible bit = 0, @Use_IsIMSVisible bit = 0, @Use_IsParticipantRequired bit = 0, @Use_IsParticipantVisible bit = 0, @Use_IsIMSParticipantVisible bit = 0, @Use_IsFax bit = 0 ) AS SET NOCOUNT ON declare @groupRID int set @groupRID = @Group_RID If(@Group_RID=0) BEGIN SELECT @groupRID = ISNULL(MAX([Group_RID]),0)+1 FROM Contact_Default_Grouping SET IDENTITY_INSERT Contact_Default_Grouping ON Insert into Contact_Default_Grouping (Group_RID) values (@groupRID) SET IDENTITY_INSERT Contact_Default_Grouping OFF END IF @Use_RID=0 BEGIN INSERT INTO Contact_Use ( Method_RID, Group_RID, Use_Name, Use_Description, Use_Help, Use_IsRequired, Use_CanDelete, DateModified, Entity_Type, Use_IsIMSVisible, Use_IsVisible, Use_IsParticipantRequired, Use_IsParticipantVisible, Use_IsIMSParticipantVisible, Use_IsFax ) VALUES ( @Method_RID, @groupRID, @Use_Name, @Use_Description, @Use_Help, @Use_IsRequired, @Use_CanDelete, @DateModified, @Entity_Type, @Use_IsIMSVisible, @Use_IsVisible, @Use_IsParticipantRequired, @Use_IsParticipantVisible, @Use_IsIMSParticipantVisible, @Use_IsFax ) END ELSE BEGIN UPDATE Contact_Use SET Group_RID = @groupRID, Use_Name = @Use_Name, Use_Description = @Use_Description, Use_Help = @Use_Help, Use_IsRequired = @Use_IsRequired, Use_CanDelete = @Use_CanDelete, DateModified = @DateModified, Entity_Type = @Entity_Type, Use_IsIMSVisible = @Use_IsIMSVisible, Use_IsVisible =@Use_IsVisible, Use_IsParticipantRequired = @Use_IsParticipantRequired, Use_IsParticipantVisible = @Use_IsParticipantVisible, Use_IsIMSParticipantVisible = @Use_IsIMSParticipantVisible, Use_IsFax = @Use_IsFax WHERE Use_RID = @Use_RID END SET NOCOUNT OFF
- Changed Type Tom PhillipsModerator Wednesday, February 27, 2013 7:22 PM
Thursday, February 28, 2013 2:26 AM
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
Please do not use the 1970's Sybase “sp_” prefix unless this is a system procedure. The correct convention for a procedure name is “<verb>_<object>”, but this minor compared the other fundamental errors you have in this mess. BIT flags in SQL? NO! That was assembly language.
>> I want to insert multiple records [sic: rows are not records!] in a database and the stored procedure below is inserting only one? How should I modify it? <<
You put the parameter list into a row constructor, so of course there is only one row.
Look at my credentials. Consider how bad this code has to be for me to tell you to throw out your entire schema. Your schema, coding and mindset are fundamentally wrong. Bit flags, IDENTITY, procedrural IF-THEN flow logic, etc. You have no idea how to write SQL or how RDBMS works. Please stop trying to program SQL until you learn how.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Thursday, February 28, 2013 3:08 AM
Using IDENTITY_INSERT here really is not good. The whole point of IDENTITY is that you don't have to do MAX(ID)+1 to generate a surrogate key value. Just insert the row and retrieve the generated IDENTITY value with SCOPE_IDENTITY().
To answer your question, change the stored procedure to take a Table Valued Parameter instead of a single row of scalar values.
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Thursday, February 28, 2013 3:09 AM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Thursday, March 07, 2013 12:31 AM