Konu: Son zamanlarda gordugum en iyi kodlardan birisi
Benim isime cok yaradi. Aciklamalarina girmeden ve unutmadan paylasayim istedim - farkederseniz daha kodu duzenlemeyi bitirmedim bile ama cok iyi unutmadan koyayim buraya;)
Jeff Moden'e giyabinda tesekkur ediyorum.
start=SECONDS()
LOCAL ARRAY aArrayParm[50000]
FOR ix = 1 TO 50000
aArrayParm[m.ix] = m.ix
endfor
TEXT TO lcSplitter noshow
--
--"Monster" Split in SQL Server 2005
--Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays
--By Jeff Moden, 2008/05/22
--
CREATE procedure #splitter @Parameter VARCHAR(MAX)
as
--===== Add start and end commas to the Parameter so we can handle
-- all the elements the same way
SET @Parameter = ','+@Parameter +','
;
WITH cteTally AS(
--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@Parameter))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS Value
FROM cteTally
WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ','
--Notice how we find the comma
ENDTEXT
loConnection = Createobject('AdoDb.Connection')
loConnection.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;"+;
"Initial Catalog=AccuFRCC;Data Source=193.140.148.171\sqlexpress;"
loConnection.Open()
Local loCommand As ADODB.Command
loCommand = Createobject('AdoDb.Command')
loCommand.ActiveConnection = loConnection
loCommand.CommandText=m.lcSplitter
loCommand.Execute()
lcCurType = Type('aArrayParm[1]')
IF m.lcCurType $ 'DT'
FOR ix = 1 TO ALEN(aArrayParm)
aArrayParm[m.ix] = IIF(m.lcCurType = 'D',;
TRANSFORM(DTOC(aArrayParm[m.ix],1),'@R 9999/99/99'),;
TRANSFORM(TTOC(aArrayParm[m.ix],1),'@R 9999/99/99 99:99:99'))
endfor
endif
lcValueList = ''
* lcPrePostFix = Iif(Type('aArrayParm[1]') = 'C', "'",'')
For ix=1 To Alen(aArrayParm)
lcValueList = m.lcValueList + ;
IIF(m.ix>1,',','') + ;
TEXTMERGE('<<aArrayParm[m.ix]>>')
Endfor
* lcValueList = m.lcValueList + ')'
*!* If Lower(m.lcValueList) == '.null.'
*!* lcValueList = 'null'
*!* Endif
*!* tcCmd = Strtran(m.tcCmd,'(?)',m.lcValueList,1,1)
loCommand.CommandType = 4
loCommand.Parameters.Append( loCommand.CreateParameter("@parameter",201,1,-1, m.lcValueList ))
loCommand.CommandText = '#splitter'
oRS = loCommand.Execute
? SECONDS()-m.start, oRS.RecordCount, LEN(m.lcValueList)
ShowRS(oRS)
oRS.Close()
loConnection.Close()