How to write function in SQL - source in VBA

Question

• I  have a function written in VBA. This function works fine.

```Public Function InsertPairs(ByVal n As Integer, ByVal k As Integer, ByVal TableName As String) As Integer
Dim max As Double
Dim idx() As Double
Dim i, j, m, kt, t As Integer
Dim strLine As String
Dim strCmd As String
Dim asNumbers() As String
max = Ckn(n, k)
If max < 1 Then
InsertPairs = -1
Exit Function
End If
ReDim idx(k - 1)
For i = 1 To k
idx(i - 1) = i
Next
i = 1
m = UBound(idx)
Do
strLine = ""
For j = LBound(idx) To UBound(idx)
strLine = strLine & CStr(idx(j)) & ","
Next
strLine = VBA.Left(strLine, Len(strLine) - 1)
strCmd = "INSERT INTO " & TableName & " ("
For t = 1 To k
strCmd = strCmd & "L" & VBA.Trim\$(VBA.Str\$(t)) & ","
Next t
strCmd = VBA.Left(strCmd, Len(strCmd) - 1)
strCmd = strCmd & ") VALUES (" & strLine & ")"
DoCmd.RunSQL strCmd
m = UBound(idx)
kt = 1
If max = 1 Then Exit Do
If idx(UBound(idx)) = n Then
test:
idx(m - kt) = idx(m - kt) + 1
For j = kt - 1 To 0 Step -1
idx(m - j) = idx(m - j - 1) + 1
Next
If idx(m) > n Then
kt = kt + 1
GoTo test
End If
Else
idx(UBound(idx)) = idx(UBound(idx)) + 1
End If
max = max - 1
i = i + 1
Loop While max > 0
End Function
```

How to convert this function to SQL language

Ckn function calculate combination k elements from n and I already have this function written in SQL. The function written in VBA writes all combination in to table.

Saturday, November 13, 2010 6:50 PM

• Hi TheTrace,

Most people in this forum are not VBA experts. Could you please let us know what the difficulties you were facing when convert this VBA function to SQL?

Currently I would like to give you some hints on the conversion:

1. Implement a SQL stored procedure to replace this function, http://msdn.microsoft.com/en-us/library/ms187926.aspx.

2. For array in your function, declare a table variable with one colume. You can refer to this article for a table variable declaration http://msdn.microsoft.com/en-us/library/ms188927.aspx.

3. You can declare a varchar(max) typed variable in SQL for your "strCmd" variable, and then use "exec sp_executeSQL @strSQL" to execute the dynamic SQL, http://msdn.microsoft.com/en-us/library/ms188001.aspx.

4. For string functions regarding trim, left, and right etc in SQL, you can refer to this article, http://msdn.microsoft.com/en-us/library/ms181984.aspx

5. For loop control in SQL, you can refer to http://msdn.microsoft.com/en-us/library/ms178642.aspx

Please try to convert your function to SQL based on the above hints. If you encounter any difficulty, please post it out so that we can give you further assistance.

Thursday, November 18, 2010 4:10 AM
• Choose  function calculates the combinations k elements from n .. writes all combination into a table.

For other readers, here are the Combination and Permutation formulas:

In combinations, the order does not matters:

C(n,k) = n! /(k! * (n-k)!)

In permutations, the order matters:

P(n,k) = n! /((n-k)!)

SQL is a declarative language, so we do not want loops or procedural code. Firsts we need to create the big set of the (n) elements:

CREATE TABLE N-elements

(n CHAR(1) NOT NULL PRIMARY KEY);

If we display the combinations or permutations  in a row, need to know (k). Let's go with (k=3) and start with Permutations. While there are more of them, they are easy to do

CREATE TABLE Perms_3

(k1 CHAR(1) NOT NULL,

k2 CHAR(1) NOT NULL,

k3 CHAR(1) NOT NULL,

PRIMARY KEY (k1, k2, k3));

INSERT INTO Perms_3 (k1, k2, k3)

SELECT DISTINCT N1.n, N2.n, N3.n

FROM N-elements AS N1,

N-elements AS N2,

N-elements AS N3;

We do the same CROSS JOIN for combinations, but we sort the data :

CREATE TABLE Comb_3

(k1 CHAR(1) NOT NULL,

k2 CHAR(1) NOT NULL,

k3 CHAR(1) NOT NULL,

PRIMARY KEY (k1, k2, k3));

INSERT INTO Comb_3 (k1, k2, k3)

SELECT DISTINCT N1.n, N2.n, N3.n

FROM N-elements AS N1,

N-elements AS N2,

N-elements AS N3

WHERE N1.n < N2.n

AND N2.n < N3.n;

--CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
Friday, November 19, 2010 9:03 PM

All replies

• Hi TheTrace,

Most people in this forum are not VBA experts. Could you please let us know what the difficulties you were facing when convert this VBA function to SQL?

Currently I would like to give you some hints on the conversion:

1. Implement a SQL stored procedure to replace this function, http://msdn.microsoft.com/en-us/library/ms187926.aspx.

2. For array in your function, declare a table variable with one colume. You can refer to this article for a table variable declaration http://msdn.microsoft.com/en-us/library/ms188927.aspx.

3. You can declare a varchar(max) typed variable in SQL for your "strCmd" variable, and then use "exec sp_executeSQL @strSQL" to execute the dynamic SQL, http://msdn.microsoft.com/en-us/library/ms188001.aspx.

4. For string functions regarding trim, left, and right etc in SQL, you can refer to this article, http://msdn.microsoft.com/en-us/library/ms181984.aspx

5. For loop control in SQL, you can refer to http://msdn.microsoft.com/en-us/library/ms178642.aspx

Please try to convert your function to SQL based on the above hints. If you encounter any difficulty, please post it out so that we can give you further assistance.

Thursday, November 18, 2010 4:10 AM
• the procedure generate combination all k elements from n, so it's typical statistic problem and I'd like some procedure that can do this in sql. So if I pass values  k=2 n=6 I'd like to have result like this

1,2

1,3

1,4

1,5

1,6

2,3

2,4

...

5,6

So I need some help how to write a function in SQL.

Friday, November 19, 2010 5:49 PM
• Choose  function calculates the combinations k elements from n .. writes all combination into a table.

For other readers, here are the Combination and Permutation formulas:

In combinations, the order does not matters:

C(n,k) = n! /(k! * (n-k)!)

In permutations, the order matters:

P(n,k) = n! /((n-k)!)

SQL is a declarative language, so we do not want loops or procedural code. Firsts we need to create the big set of the (n) elements:

CREATE TABLE N-elements

(n CHAR(1) NOT NULL PRIMARY KEY);

If we display the combinations or permutations  in a row, need to know (k). Let's go with (k=3) and start with Permutations. While there are more of them, they are easy to do

CREATE TABLE Perms_3

(k1 CHAR(1) NOT NULL,

k2 CHAR(1) NOT NULL,

k3 CHAR(1) NOT NULL,

PRIMARY KEY (k1, k2, k3));

INSERT INTO Perms_3 (k1, k2, k3)

SELECT DISTINCT N1.n, N2.n, N3.n

FROM N-elements AS N1,

N-elements AS N2,

N-elements AS N3;

We do the same CROSS JOIN for combinations, but we sort the data :

CREATE TABLE Comb_3

(k1 CHAR(1) NOT NULL,

k2 CHAR(1) NOT NULL,

k3 CHAR(1) NOT NULL,

PRIMARY KEY (k1, k2, k3));

INSERT INTO Comb_3 (k1, k2, k3)

SELECT DISTINCT N1.n, N2.n, N3.n

FROM N-elements AS N1,

N-elements AS N2,

N-elements AS N3

WHERE N1.n < N2.n

AND N2.n < N3.n;

--CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
Friday, November 19, 2010 9:03 PM