Son zamanlarda gordugum en iyi kodlardan birisi

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()