CODEWITHSUNDEEP
sql-servervbaexcel
Dave
Dave

Reputation: 19

VBA - SQL Query String

I have declared variables that store the name of columns from a SQL Table as well as variables that store their corresponding "values to find".

Dim sColumn1 As String
Dim sColumn2 As String
Dim sColumn3 As String
Dim sValue1 As String
Dim sValue2 As String
Dim sValue3 As String

sColumn1 = Sheets(1).Range("A1").Value
sColumn2 = Sheets(1).Range("B1").Value
sColumn3 = Sheets(1).Range("C1").Value
sValue1 = Sheets(1).Range("A2").Value
sValue2 = Sheets(1).Range("B2").Value
sValue3 = Sheets(1).Range("C2").Value

I want to make a dynamic query like this:

StrQuery = "SELECT * FROM dbo.Table1 WHERE ('" & sColumn1 & "') LIKE ('" & sValue1 & "') AND ('" & sColumn2 & "') LIKE ('" & sValue2 & "') AND ('" & sColumn3 & "') LIKE ('" & sValue3 & "')"

This code does not generate any errors but IT DOES NOT pull any records either. I have confirmed and all the variables are being assigned the right values.

The query above works fine if I replace the Column variables for the actual column names in the SQL Table. Like this:

StrQuery = "SELECT * FROM dbo.Table1 WHERE Column1 LIKE ('" & sValue & "') AND Column2 LIKE ('" & sValue2 & "') AND Column3 LIKE ('" & sValue3 & "')"

With this string I get results without any problem but the columns will be dynamic. Users will choose from a variety of 15 columns.

Why is it that when I use the Variable it does not work even though I know the value of the variables matches exactly the names of the Columns in the SQL Table?

Am I using the wrong format in the string so that it reads the actual value stored within the variables?

Upvotes: 1

Views: 3230

Answers (2)

M.Doerner
M.Doerner

Reputation: 722

The answer by @TimWilliams should address the problem of not getting any result, provided the input is valid. However, as stated in the comments, the code is a bit brittle because entering malformed or otherwise inappropriate values into the fields of the sheet might produce interesting results. So, I would like to suggest a somewhat more robust approach than simply executing the SQL string.

If you are using ADO to talk to the SQL Server, you can call stored procedures on it as explained in this SO answer. Furthermore, provided you are at least on SQL Server 2008, there is the stored procedure sp_executesql. This stored procedure allows you to execute a SQL string containing parameters. The first parameter is the SQL string, the second a string containing the parameter list and the following parameters are the actual parameters for the query. This allows you to pass in the strings representing the LIKE pattern as actual string parameters. So, no matter what the values are, they cannot break the query itself.

Regarding the column names, you should at least escape them with square brackets. That is not waterproof, but it already goes a long way regarding accidentally malformed values.

The result would be something like

sqlString = "SELECT * FROM dbo.Table1 " & _
            "WHERE [" & sColumn1 & "] LIKE @value1 " & _
            "    AND [" & sColumn2 & "] LIKE @value2 " & _
            "    AND [" & sColumn3 & "] LIKE @value3 "

parameterDeclarationString = "@value1 AS NVARCHAR(1000)," & _
                             "@value2 AS NVARCHAR(1000)," & _
                             "@value3 AS NVARCHAR(1000)"

Note that the max length of the parameters is just an arbitrary guess for a sensible upper limit.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166181

Warnings above about using parameterized queries still apply but this is how you would get this to work:

StrQuery = "SELECT * FROM dbo.Table1 WHERE " & _
                      sColumn1 & " LIKE ('%" & sValue & "%')" & _
            " AND " & sColumn2 & " LIKE ('%" & sValue2 & "%')" & _
            " AND " & sColumn3 & " LIKE ('%" & sValue3 & "%')"

Upvotes: 3

Related Questions