Group Insert Into SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group Insert Into SP

Author

Topic

M2
Starting Member

22 Posts

Posted - 2003-06-08 : 22:45:05

I have a need to run a stored procedure, which will insert multiple rows (Category + it¡¦s parent Category) in the database. The problem is that I don't know how much inserts I need to do. I need to pass in the stored procedure a few category records and loop over it. I have read the http://www.sqlteam.com/item.asp?ItemID=637
and http://www.sqlteam.com/item.asp?ItemID=256 . However, I still very confuse. Do have any simple way to do this?

I have a Category table as following:

ID CategoryName ParentCategory
1 Food 0
2 Japanese Food 1
3 Italian Food 1
4 Chinese Food 1
5 Country 0
6 Malaysia 5
7 USA 5
8 AUS 5

I will get lists of data as following from User:

Main Category: A
Sub Category: 1
Sub Category: 2
Sub Category: 3
.
.
.
N

My Current Store Procedure :

DECLARE @Main int
DECLARE @Main1 int
DECLARE @Count int
DECLARE @ToTalCount int
DECLARE @N varchar(50)
DECLARE @P int

SET @Count = '1'
SET @TotalCount = '3'
SET @N = 'A'
SET @P = '0'

WHILE @Count < @ToTalCount
BEGIN
--This SP can only insert 1 category record
Exec Category$Insert
@Name = @N,
@ParentCategoryID = @P,
@MainID = @Main OUTPUT

If @Count = '1'
BEGIN
Set @Main1 = @Main
END

Set @Count = @Count + 1
Select @Main1 as 'Main Category ID'

Continue

END

How do I pass the above category info into database (Store Produce)?
Could anyone please guide me on this? Thanks!

M2

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 23:25:48

It will help a lot if you will post the table structure and name of the target table that will be the object of the INSERT?

Sam

M2
Starting Member

22 Posts

Posted - 2003-06-08 : 23:36:20

Thank for your help, these is my INSERT Table & SP. Rdgs, M2
===================================================================

CREATE TABLE [Category] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCategoryID] [int] NULL ,
[LastModifyDate] [datetime] NOT NULL CONSTRAINT [DF_Category_LastModifyDate] DEFAULT (getdate()),
[Status] [bit] NOT NULL CONSTRAINT [DF_Category_Status] DEFAULT (1),
[Remarks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

==================================================================
CREATE PROCEDURE Category$MultiInsert

@Name varchar(50),
@ParentCategoryID int,
@MainID int OUTPUT

AS

DECLARE @newid varchar(4)

IF not exists ( SELECT * FROM dbo.Category WHERE Name = @Name and ParentCategoryID = @ParentCategoryID )

BEGIN
SET NOCOUNT ON

INSERT INTO dbo.Category (Name, ParentCategoryID)
VALUES (@Name,
@ParentCategoryID )

Select @MainID = @@Identity

ELSE

BEGIN
SET NOCOUNT ON
SELECT 'True' AS DuplicateRecord
END
GO

M2
Starting Member

22 Posts

Posted - 2003-06-09 : 21:11:52

I've post my table structure and SP here, could anyone please help me on this ?

* my brain cells are dying.........

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-09 : 21:59:07

Hi M2,

The table structure and stored procedure are helpful, but I'm not clear on your problem. Let me try to restate it here.

The procedure Category$MultiInsert will insert 1 row into dbo.Category if that row doesn't exist. You want help inserting multiple rows, but you don't know how many?

The needed information to begin assisting with your problem is: What is the source of the rows to be inserted? Is it a recordset? User input from an ASP page? Another table?

Looking forward to your response.

Sam

M2
Starting Member

22 Posts

Posted - 2003-06-09 : 22:48:44

I want accept an ARRAY from ASP (MainCategory, SubCategory1, SubCategory2, SubCategory....n), then based on the received ARRAY, i insert the ARRAY into the Database.....

For my current status, i can only accept the single parameter from ASP.
Example:
Let said, i have 3 record like this :
'Food', 'Japanese Food', 'Italian Food'
ASP have to run SP and pass the record 3 times.
Pass the 'Food'into SP(Category$MultiInsert), get the @@identity, then pass the 'Japanese Food' into SP(Category$MultiInsert)again, set the ParentCategoryID to previous @@Identity, and pass the 'Italian Food'into SP(Category$MultiInsert), set the ParentCategoryID to previous @@Identity

What i need help is : MY PROBLEM
Create a SP, which can accept multiple record - Array (MainCategory, SubCategory1, SubCategory2, SubCategory....n) from ASP. The SP will accept and run the ARRAY , and insert the array into the database. SP just run 1 time only.

ARRAY(1,2,3,4,5,......n)

I dont know how many SubCategory will have.
ARRAY(1) always 'MainCategory'
ARRAY(2....n) = 'SubCategory'

After insertion :

dbo.Category Table
ID CategoryName ParentCategory
1 Food 0
2 Japanese Food 1
3 Italian Food 1

M2

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-09 : 23:32:21

This looks like you want to pass the data from ASP to SQL in parameters as CSV strings.

It looks like you can build a CSV string in ASP and pass it in a VARCHAR parameter to your stored procedure as:

'Food, Japanese Food, Italian Food'

There are lots of ways to do this, and I look forward to reading about some more efficent methods than this.

My solution would probably start with the loop posted by Graz

Create procedure sp_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
-- Created by graz
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
select Array_Value = @array_value

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end

set nocount off
go

The first CSV token is your MainCategory, while following tokens are SubCategories. It make make sense to parse the MainCategory prior to entering the loop:

INSERT INTO dbo.Category (Name, ParentCategoryID)
VALUES (@FirstToken, 0)
SET @ParentCategoryID = @@ROWCOUNT

From here, you fall into the loop parsing all subsequent SubCategories which are inserted like this:

INSERT INTO dbo.Category (Name, ParentCategoryID)
VALUES (@NextToken, @ParentCategoryID)

I hope this helps. If you need help developing the completed procedure, let me know.

Sam

Edited by - SamC on 06/09/2003 23:53:10

M2
Starting Member

22 Posts

Posted - 2003-06-10 : 00:39:10

Thanks for guide me through this. It's really help me a lot. Thanks !

However, before entering the loop and insert the @First/NextToken, how to separate the array into @FirstToken, and @NextToken?

Could you please help me developing that procedure? Thanks!

M2

M2
Starting Member

22 Posts

Posted - 2003-06-10 : 04:07:10

Dear Sam,

Based on the information that you provided and study in detail, I've knew/found the solution.

Thanks for your help ! Thank You Very Much !

M2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources