CODEWITHSUNDEEP
.netsql-server-2008
Ezi
Ezi

Reputation: 2210

How to pass in a SQL parameter when its too long

I have a very big 'Where' statement that's longer then the maximum allowed.

Currently I build the full sql in the vb.net code and I execute it.

The problem is that this way is known to be slow because its not a compiled query.

Is there any other way to do it?

EDIT: 

  For i As Integer = 0 To List.Count - 1
        If Filter = String.Empty Then
            Filter = " Where OrderID = "
        Else
            Filter += " OR OrderID ="
        End If
        Filter = Filter + "(" & List(i) + ")"
    Next

    Sql = "Select * from OrderPickSheet " & Filter & " And Status > -1 Order by SortOrder"
    cmd.CommandText = Sql
    cmd.ExecuteReader()

Upvotes: 2

Views: 2243

Answers (3)

Edmund Schweppe
Edmund Schweppe

Reputation: 5132

You can save a fair amount of length in the SQL command (and protect yourself from SQL injection attacks, to boot) by building a set of parameters dynamically:

    Dim paramList As New StringBuilder
    For i As Integer = 0 To List.Count - 1
        Dim paramName As String = String.Format("@p{0}", i)
        If paramList.Length > 0 Then
            paramList.Append(",")
        End If
        paramList.Append(String.Format(paramName))
        cmd.Parameters.AddWithValue(paramName, List(i))
    Next
    cmd.CommandText = "Select * from OrderPickSheet Where OrderID in (" & paramList.ToString() & ") And Status > -1 Order by SortOrder"
    cmd.ExecuteReader()

Upvotes: 1

Jeff Ogata
Jeff Ogata

Reputation: 57803

Instead of constructing the SQL statement on the client, you can call a stored procedure and pass the values in a DataTable. In your stored procedure, join to the values that are passed in, which means you won't need to build a long dynamic sql statement, and mitigates the risk of sql injection attack.

For example, if you create this type and procedure in your database (I'm guessing here as to the data types):

create type dbo.OrderIdInfo as table
(
    OrderId nvarchar(20)
)

create procedure dbo.SelectOrders
(
    @orderIds dbo.OrderIdInfo readonly
)
as

    select o.* 
    from OrderPickSheet o
        inner join @orderIds i on o.OrderId = i.OrderId
    where o.Status > -1
    order by o.SortOrder

You can call the procedure as shown below. The code does run (tested in LINQPad), but VB is not my primary language, so forgive any clumsiness:

Dim table As New DataTable
Dim results As New DataTable

table.Columns.Add("OrderId", GetType(String))

table.Rows.Add("1")
table.Rows.Add("2")
table.Rows.Add("4")

Dim parameter As New SqlParameter("@orderIds", SqlDbType.Structured)

parameter.Value = table

Using connection As SqlConnection = New SqlConnection("server=localhost;database=Test;integrated security=true")
    Using command As SqlCommand = connection.CreateCommand()

        command.CommandText = "dbo.SelectOrders"
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add(parameter)

        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        results.Load(reader)

    End Using
End Using

Upvotes: 2

Ed B
Ed B

Reputation: 6054

You can create the SQL dynamically in a stored procedure instead of in .NET.

The query still won't be compiled, but you can pass all your variables in via comma delimited string or XML

Upvotes: 1

Related Questions