수행 방식
1 - exec usp_UpdateTablewithArray 'tblTestTable', 'dcColumn1',
'string1;string2;string3;string4;' , ';' , @pcRtnVal output
이런 구문이 아래와 같은 insert 문으로 변환 됩니다.
will do the following inserts:
Insert into tblTestTable (dcColumn1) values (String1)
Insert into tblTestTable (dcColumn1) values (String2)
Insert into tblTestTable (dcColumn1) values (String3)
Insert into tblTestTable (dcColumn1) values (String4)
2 - exec usp_UpdateTablewithArray 'tblTestTable', 'dcColumn1,dcColumn2',
'string1;string2;string3;string4;' , ';' , @pcRtnVal output
이런 구문이 아래와 같은 insert 문으로 변환 됩니다.
will do the following inserts:
Insert into tblTestTable (dcColumn1, dcColumn2) values
(String1,String2)
and
Insert into tblTestTable (dcColumn1, dcColumn2) values
(String3,String4)
--샘플 테이블 생성
create table test1(
c1 varchar(10)
, c2 varchar(10)
)
--프로시져 수행
declare @pcRtnVal varchar(300)
exec usp_UpdateTablewithArray 'test1', 'c1,c2'
, 'asd;asf;asg;ash;' , ';' , @pcRtnVal output
--데이터 조회
select * from test1
/*
Stored Procedure:
usp_UpdateTablewithArray
Creation Date:
27/06/2001
Written by:
Matt Tracey
EMail by:
matthew.tracey@dataconversion.ie
Purpose:
To insert a one dimensional or multi-dimensional array of
Type Character into a Fields of type Character in a Table
Input Parameters:
@pcTableName varchar(50) -
Table to insert into
@pcFieldArray varchar(300) -
Fields in Table to Insert values into
@pcArray varchar(2000) -
Array of Values
@pcArrayDelimiter char (1) -
Delimiter for Array Values
Output Parameters:
@pcRtnVal varchar (300) output -
If Empty OK
Else Holds Error
Return Status:
@lnError -
Holds Error Number
0 = OK
Usage:
exec usp_UpdateTablewithArray 'tblTestTable', 'dcColumn1,dcColumn2',
'asd;asf;asg;ash;' , ';' , @pcRtnVal output
Local Variables:
declare @lnError int
-- Return Error Value
declare @lcArray varchar(2000)
-- Array to Import
declare @lcSQLString nvarchar(2000)
-- Holds SQL Update Statement
declare @lcNumberofFields int
-- Number of Fields to Import into
declare @lcNumberofElements int
-- Number of Elements being imported
declare @lcUpdateString varchar(1000)
-- Holds SQL Update Values String
declare @lcTempString varchar(100)
-- Holds an Array Value
declare @lnCounter int
-- Used as a Counter to cycle through the number of fields
Called By: Nothing Yet
Calls: Nothing
*/
CREATE procedure usp_UpdateTablewithArray
(
@pcTableName varchar(50),
@pcFieldArray varchar(300),
@pcArray varchar(2000),
@pcArrayDelimiter char (1),
@pcRtnVal varchar (300) output
)
as
set nocount on
declare @lnError int
-- Return Error Value
declare @lcArray varchar(2000)
-- Array to Import
declare @lcSQLString nvarchar(2000)
-- Holds SQL Update Statement
declare @lcNumberofFields int
-- Number of Fields to Import into
declare @lcNumberofElements int
-- Number of Elements being imported
declare @lcUpdateString varchar(1000)
-- Holds SQL Update Values String
declare @lcTempString varchar(100)
-- Holds an Array Value
declare @lnCounter int
-- Used as a Counter to cycle through the number of fields
-- Initialise Error and Return Strings
set @lnError = 0
set @pcRtnVal = ''
-- Copy Parameter Array to Local Array
set @lcArray = rtrim(ltrim(@pcArray))
-- Check we have a string of Account Numbers to import
if @lcArray = ''
begin
set @pcRtnVal = 'Input Error: Array to Import.'
return
end
-- Get the number of Fields to Update
declare @lcTempstr varchar(300)
set @lcTempstr = @pcFieldArray
select @lcNumberofFields = ( (len(@lcTempstr)-
len(replace(@lcTempstr, ',', ''))) / len(',') ) +1
if @lcNumberofFields = 0
begin
set @pcRtnVal = 'Input Error: No Field List.'
return
end
-- Get the number of Elements in the array
declare @lcTempstr2 varchar(2000)
set @lcTempstr2 = @pcArray
select @lcNumberofElements = ( (len(@lcTempstr2)-
len(replace(@lcTempstr2, @pcArrayDelimiter, '')))
/ len(@pcArrayDelimiter) )
if @lcNumberofElements%@lcNumberofFields <> 0
begin
set @pcRtnVal =
'Input Error: Number of Elements will not fit in Fields.'
return
end
-- Check that there is a trailing delimiter in the Array, if not Add one
if right(@lcArray,1) <> @pcArrayDelimiter
select @lcArray = @lcArray + @pcArrayDelimiter
-- Parse the @lcArray string into the table @pcTableName
while charindex(@pcArrayDelimiter, @lcArray) > 0
begin
-- Get next Elements for the NumberofFields from the Array
-- and place in @lcUpdateString
set @lcUpdateString = ''
set @lnCounter = 1
while (@lnCounter <= @lcNumberofFields)
begin
select @lcTempString = substring(@lcArray,1,
charindex(@pcArrayDelimiter, @lcArray)-1)
select @lcArray = substring(@lcArray,len(rtrim(@lcArray)) -
((len(rtrim(@lcArray)) - len(rtrim(@lcTempString)))
- 2), 2000)
set @lcUpdateString = @lcUpdateString + char(39) + @lcTempString
+ char(39) + ','
set @lnCounter = @lnCounter +1
end
-- Remove the trailing character of the Updatestring
set @lcUpdateString = left(@lcUpdateString,len(@lcUpdateString)-1)
-- Build the SQL Statement
set @lcSQLString = N'insert into '+ @pcTableName +
' (' + @pcFieldArray + ')' +
' values (' + @lcUpdateString + ')'
-- Execute the SQL Statement and check for an Error
exec sp_executesql @lcSQLString
set @lnError = @@error
if @lnError <> 0
break
end
-- Check for any errors
if @lnError <> 0
set @pcRtnVal = 'Database Error: Updating Table with Array.'
set nocount off
-- Return Error Code
return @lnError