Stored procedure in SQL + vba to return a subset in Excel based onLIKE %varchar% clause

[Mahen]

I am using MS Excel 2003 vba to return a subset of Suppliers
(Northwind database) with "exo" in their names. The query returns
names
with "e", "x", "o", "ex", "xo", "exo". . . not exactly what I want!
Details as follows:

1) Stored procedure in Northwind database
CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar
)
as
SELECT TOP 100 PERCENT SupplierID, CompanyName
FROM dbo.Suppliers
WHERE (CompanyName LIKE '%'+@val+'%')
ORDER BY SupplierID
GO


2)VBA code
2a)
Sub FilterData(wkS As Worksheet, rngStart As Range, rADO As Range,
val
As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim qryD As QueryTable
Dim sSQL As String
Dim sADO As String
Range(rngStart, rngStart.End(xlDown).End(xlToRight)).Clear
sADO = ""
Do
If IsEmpty(rADO) Then Exit Do
sADO = sADO & " " & rADO.Value
Set rADO = rADO.Offset(1, 0)
Loop
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.Open sADO
Set rs = .Execute(sSQL)
End With
Set qryD = wkS.QueryTables.Add(rs, rngStart)
With qryD
.RefreshStyle = xlOverwriteCells
.Refresh
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub


2b) Worksheet_Change trigger in sheet "Suppliers"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$1" Then
Application.EnableEvents = False
FilterData _
ActiveWorkbook.Sheets("Suppliers"), _
ActiveWorkbook.Sheets("Suppliers").Range("a1"), _
ActiveWorkbook.Sheets("sql").Range("rngADOnw"), _
Target.Value
Application.EnableEvents = True
End If
End Sub


2c) ActiveWorkbook.Sheets("sql").Range("rngADOnw") contains the
following:
Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=
Northwind


2d) val is a value type in cell E1 on sheet "Suppliers".


Thanks for your help

 

[joel]

You need to put the percent signs into your SQL

from
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
to
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '%" & val & "%';"

 

[Mahen]

You need to put the percent signs into your SQL

from
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
to
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '%" & val & "%';"

I am using MS Excel 2003 vba to return a subset of Suppliers
(Northwind database) with "exo" in their names. The query returns
names
with "e", "x", "o", "ex", "xo", "exo". . . not exactly what I want!
Details as follows:

1) Stored procedure in Northwind database
CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar
)
as
SELECT     TOP 100 PERCENT SupplierID, CompanyName
FROM         dbo.Suppliers
WHERE     (CompanyName LIKE  '%'+@val+'%')
ORDER BY SupplierID
GO

2)VBA code
2a)
Sub FilterData(wkS As Worksheet, rngStart As Range, rADO As Range,
val
As String)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim qryD As QueryTable
    Dim sSQL As String
    Dim sADO As String
    Range(rngStart, rngStart.End(xlDown).End(xlToRight)).Clear
    sADO = ""
    Do
        If IsEmpty(rADO) Then Exit Do
        sADO = sADO & " " & rADO.Value
        Set rADO = rADO.Offset(1, 0)
    Loop
    sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
    Set cn = New ADODB.Connection
    With cn
        .CursorLocation = adUseClient
        .Open sADO
        Set rs = .Execute(sSQL)
    End With
    Set qryD = wkS.QueryTables.Add(rs, rngStart)
    With qryD
        .RefreshStyle = xlOverwriteCells
        .Refresh
    End With
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

2b) Worksheet_Change trigger in sheet "Suppliers"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$1" Then
        Application.EnableEvents = False
        FilterData _
        ActiveWorkbook.Sheets("Suppliers"), _
        ActiveWorkbook.Sheets("Suppliers").Range("a1"), _
        ActiveWorkbook.Sheets("sql").Range("rngADOnw"), _
        Target.Value
        Application.EnableEvents = True
    End If
End Sub

2c) ActiveWorkbook.Sheets("sql").Range("rngADOnw") contains the
following:
Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=
Northwind

2d) val is a value type in cell E1 on sheet "Suppliers".

Thanks for your help- Hide quoted text -

- Show quoted text -

Thank you for your reply. I have tried this before and it does not
seem to work. Did I miss out on any references to object
libraries . . . .?

 

[joel]

I don'g know why you just can't put you SQL directly into the excel code?
the SQL that works in Access. In Excel I usually get my SQL statements
working by using the SQL editor and entering one parameter at a time until it
works.

First I go to the worksheet and set up a query with no filtering

Data - Import External Data - New Database query

After I set up a simple query I go and select any cell in the in the queryt.

Then I go to worksheet menu

Data. A new option gets enabled in the menu called Edit Query. In the
query editor there is a button labeled SQL. I then put my parameter into the
SQL editor. If I make an error the editor will immediately give me an error
and I fix the error.

In excel 2007 the menus are a little different but it also has the Edit
Query menu.

Next, I enter the SQL into my macro and make sure it works. Finally I
modify the SQL to add paramerter like in your case the worksheet val and
_Filter.

The WHERE, FROM, LIKE will work in Excel just like in Access.

You need to put the percent signs into your SQL

from
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
to
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '%" & val & "%';"

I am using MS Excel 2003 vba to return a subset of Suppliers
(Northwind database) with "exo" in their names. The query returns
names
with "e", "x", "o", "ex", "xo", "exo". . . not exactly what I want!
Details as follows:

1) Stored procedure in Northwind database
CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar
)
as
SELECT TOP 100 PERCENT SupplierID, CompanyName
FROM dbo.Suppliers
WHERE (CompanyName LIKE '%'+@val+'%')
ORDER BY SupplierID
GO

2)VBA code
2a)
Sub FilterData(wkS As Worksheet, rngStart As Range, rADO As Range,
val
As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim qryD As QueryTable
Dim sSQL As String
Dim sADO As String
Range(rngStart, rngStart.End(xlDown).End(xlToRight)).Clear
sADO = ""
Do
If IsEmpty(rADO) Then Exit Do
sADO = sADO & " " & rADO.Value
Set rADO = rADO.Offset(1, 0)
Loop
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.Open sADO
Set rs = .Execute(sSQL)
End With
Set qryD = wkS.QueryTables.Add(rs, rngStart)
With qryD
.RefreshStyle = xlOverwriteCells
.Refresh
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

2b) Worksheet_Change trigger in sheet "Suppliers"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$1" Then
Application.EnableEvents = False
FilterData _
ActiveWorkbook.Sheets("Suppliers"), _
ActiveWorkbook.Sheets("Suppliers").Range("a1"), _
ActiveWorkbook.Sheets("sql").Range("rngADOnw"), _
Target.Value
Application.EnableEvents = True
End If
End Sub

2c) ActiveWorkbook.Sheets("sql").Range("rngADOnw") contains the
following:
Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=
Northwind

2d) val is a value type in cell E1 on sheet "Suppliers".

Thanks for your help- Hide quoted text -

- Show quoted text -

Thank you for your reply. I have tried this before and it does not
seem to work. Did I miss out on any references to object
libraries . . . .?

 

[Mahen]

I don'g know why you just can't put you SQL directly into the excel code? 
the SQL that works in Access.  In Excel I usually get my SQL statements
working by using the SQL editor and entering one parameter at a time until it
works.

First I go to the worksheet and set up a query with no filtering

Data - Import External Data - New Database query

After I set up a simple query I go and select any cell in the in the queryt.

Then I go to worksheet menu

Data.  A new option gets enabled in the menu called Edit Query.  In the
query editor there is a button labeled SQL.  I then put my parameter into the
SQL editor.  If I make an error the editor will immediately give me an error
and I fix the error.

In excel 2007 the menus are a little different but it also has the Edit
Query menu.

Next, I enter the SQL into my macro and make sure it works.  Finally I
modify the SQL to add paramerter like in your case the worksheet val and
_Filter.

The WHERE, FROM, LIKE will work in Excel just like in Access.

You need to put the percent signs into your SQL
from
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
to
sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '%" & val & "%';"
:
I am using MS Excel 2003 vba to return a subset of Suppliers
(Northwind database) with "exo" in their names. The query returns
names
with "e", "x", "o", "ex", "xo", "exo". . . not exactly what I want!
Details as follows:
1) Stored procedure in Northwind database
CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar
)
as
SELECT     TOP 100 PERCENT SupplierID, CompanyName
FROM         dbo.Suppliers
WHERE     (CompanyName LIKE  '%'+@val+'%')
ORDER BY SupplierID
GO
2)VBA code
2a)
Sub FilterData(wkS As Worksheet, rngStart As Range, rADO As Range,
val
As String)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim qryD As QueryTable
    Dim sSQL As String
    Dim sADO As String
    Range(rngStart, rngStart.End(xlDown).End(xlToRight)).Clear
    sADO = ""
    Do
        If IsEmpty(rADO) Then Exit Do
        sADO = sADO & " " & rADO.Value
        Set rADO = rADO.Offset(1, 0)
    Loop
    sSQL = "EXEC mlsp_Tbl_" & wkS.Name & "_Filter '" & val & "';"
    Set cn = New ADODB.Connection
    With cn
        .CursorLocation = adUseClient
        .Open sADO
        Set rs = .Execute(sSQL)
    End With
    Set qryD = wkS.QueryTables.Add(rs, rngStart)
    With qryD
        .RefreshStyle = xlOverwriteCells
        .Refresh
    End With
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
2b) Worksheet_Change trigger in sheet "Suppliers"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$1" Then
        Application.EnableEvents = False
        FilterData _
        ActiveWorkbook.Sheets("Suppliers"), _
        ActiveWorkbook.Sheets("Suppliers").Range("a1"), _
        ActiveWorkbook.Sheets("sql").Range("rngADOnw"), _
        Target.Value
        Application.EnableEvents = True
    End If
End Sub
2c) ActiveWorkbook.Sheets("sql").Range("rngADOnw") contains the
following:
Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=
Northwind
2d) val is a value type in cell E1 on sheet "Suppliers".
Thanks for your help- Hide quoted text -
- Show quoted text -

Thank you for your reply. I have tried this before and it does not
seem to work. Did I miss out on any references to object
libraries . . . .?- Hide quoted text -

- Show quoted text -

I am currently using SQL server 2000. However, i will try out your
suggestions and see how it works. Thanks again for these.

 

[urkec]

:
I am using MS Excel 2003 vba to return a subset of Suppliers
(Northwind database) with "exo" in their names. The query returns
names
with "e", "x", "o", "ex", "xo", "exo". . . not exactly what I want!
Details as follows:

1) Stored procedure in Northwind database
CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar
)
as
SELECT TOP 100 PERCENT SupplierID, CompanyName
FROM dbo.Suppliers
WHERE (CompanyName LIKE '%'+@val+'%')
ORDER BY SupplierID
GO

I am currently using SQL server 2000. However, i will try out your
suggestions and see how it works. Thanks again for these.

Hello Mahen,

when the data length is ommited in T-SQL char and varchar declaration it
defaults to 1. See here:

http://msdn.microsoft.com/en-us/library/ms176089.aspx

So you should change your stored procedure definition to something like:

CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar (20)
)
as
SELECT TOP 100 PERCENT SupplierID, CompanyName
FROM dbo.Suppliers
WHERE (CompanyName LIKE '%'+@val+'%')
ORDER BY SupplierID
GO


The rest of your code should work as expected.

--
urkec

My blog:
http://theadminblog.blogspot.com/

My CodeProject articles:
http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=4210975

 

[Mahen]

:
I am using MS Excel 2003 vba to return a subset of Suppliers
(Northwind database) with "exo" in their names. The query returns
names
with "e", "x", "o", "ex", "xo", "exo". . . not exactly what I want!
Details as follows:
1) Stored procedure in Northwind database
CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar
)
as
SELECT     TOP 100 PERCENT SupplierID, CompanyName
FROM         dbo.Suppliers
WHERE     (CompanyName LIKE  '%'+@val+'%')
ORDER BY SupplierID
GO

I am currently using SQL server 2000. However, i will try out your
suggestions and see how it works. Thanks again for these.

Hello Mahen,

when the data length is ommited in T-SQL char and varchar declaration it
defaults to 1. See here:

http://msdn.microsoft.com/en-us/library/ms176089.aspx

So you should change your stored procedure definition to something like:

CREATE PROCEDURE [dbo].[mlsp_Tbl_Suppliers_Filter]
(
@val varchar (20)
)
as
SELECT     TOP 100 PERCENT SupplierID, CompanyName
FROM         dbo.Suppliers
WHERE     (CompanyName LIKE  '%'+@val+'%')
ORDER BY SupplierID
GO

The rest of your code should work as expected.

--
urkec

My blog:http://theadminblog.blogspot.com/

My CodeProject articles:http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=4...- Hide quoted text -

- Show quoted text -

Thank you for this. I actually solved the problem by trial & error,
but your message helps me understand why it did not work. Vive
l'internet!

 

[Tim Zych]

I don'g know why you just can't put you SQL directly into the excel code?

the SQL that works in Access.

The OP was talking about a stored procedure (SQL Server), not MS Access.

To try to give you some idea why the OP should not use dynamic SQL, from my
point of view, has to do with accessibility and ease of maintenance. Let's
say you have a user who has a support issue, and as a result of that, you
must make a change to the SQL. The change being made must be available to
everybody.

Using a stored procedure, this is very easy to deal with -- simply edit the
stored proc and execute the change. Done..end of support issue. Using
dynamic SQL, on the other hand, would require a redeployment of the client
piece of the solution -- not something that I want to deal with unless
absolutely necessary. In the real world this might manifest itself like the
following:

1. Get a support call from a user that the recordset is not returning the
correct results.
2. The developer opens the client app (let's say it is an Excel
workbook/template with macros), points the environment to production and
replicates the results.
3. The developer modifies the code, then must re-deploy the application.
4. The template is already opened by some users, so you send out an email
informing users to be sure to save their changes, close the template,
re-open (to get the latest copy), then continue.
5. Note: in the real world, users may not have the time to perform step 4
(think the financial industry where a few minutes is a small eternity -- say
this happens during a volatile time of day and they don't have time until
after the market closes Or, users are right in the middle of some work and
cannot just simply close out of everything without a serious disruption).
6. Other users continue to use your application, but they, too, get the
wrong recordset, and issue support requests.
7. The developer repeats step 4 with as many people as needed.
8. The support of this issue, which would have been 15 minutes for a
SQL-centric distribution, has turned into a 1+ hour issue, and is still not
resolved because some users were away from their desks when the step 4 email
was sent..

With a SQL-centric solution, the support issue might manifest itself like
the following:
1. Get a support call from a user that the recordset is not returning the
correct results.
2. The developer goes directly to the stored procedure, executes it and sees
the problem, then fixes it.
3. While still on the phone with the user, the developer tells the user to
click the button again. The user is happy the results are correct now.
Everybody else will get the correct results too with no changes to their
environment.

There are other reasons to avoid using dynamic SQL, relating to permissions,
SQL injection, query plans/caching, and many other things. See:

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html