Using Params with an IN Statemet...Feedback

[William Ryan]

I hate using Dynamic SQL and was prohibited from doing so b/c we couldn't
use 'IN' Statement in the where clause with Parameters which broke out data
access block. I wrote a work around which works, but I did it in haste.
I'd really like some feedback (be harsh, I'm not thin skinned) and would
like to improve it. Here's the link..
http://www.knowdotnet.com/articles/temptables.html It's based on a VARCHAR
field, but could easily be enhanced with another overload. Any feedback
would be appreciated.

Thanks,

Bill

--
Cordially,

W.G. Ryan
(e-mail address removed)
www.devbuzz.com
www.knowdotnet.com

 

[Miha Markic]

Hi Bill,

Interesting approach, indeed.
Curiosity: Did you measure the perfornamce of this approach versus dynamic
sql?

 

[William Ryan]

Thanks Miha:

Actually, performance testing to see if it's really viable for the majority
of apps is my next project - I want to do it with a bunch of more complex
queries we have in production to get a real feel for it. My first objective
was just to see if I could do it. If nothing else, it would allow you to
still exert tight control over your permissions and still encapsulate your
query.

The technique itself is fairly common and I worked at an company where we
ran Oracle and the DBA tipped me off to it so you could fire it with SQL
Plus with relative ease. I suspect though that performance should be
comparable, and if it's not, I'm going to give the same thing a try with a
derived table. I know there can be some performance problems with Temp
tables if you have a ton of data, but the amount of data it takes for it to
get ugly is usually way more than you'd ever want to deal with in Dynamic
SQL. I'm also going to try it with an Index (but I suspect that with small
number of record inserts that might have the opposite effect.) I'm going to
play with it tomorrow and I'll post a prologue.

Thanks again,

Bill

 

[Miha Markic]

Hi Bill,

Thanks Miha:

Actually, performance testing to see if it's really viable for the majority
of apps is my next project - I want to do it with a bunch of more complex
queries we have in production to get a real feel for it. My first objective
was just to see if I could do it. If nothing else, it would allow you to
still exert tight control over your permissions and still encapsulate your
query.
Indeed.

The technique itself is fairly common and I worked at an company where we
ran Oracle and the DBA tipped me off to it so you could fire it with SQL
Plus with relative ease.

Don't tell me that you've actually used Sql Plus. Aaarargh.

I suspect though that performance should be

comparable, and if it's not, I'm going to give the same thing a try with a
derived table. I know there can be some performance problems with Temp
tables if you have a ton of data, but the amount of data it takes for it to
get ugly is usually way more than you'd ever want to deal with in Dynamic
SQL. I'm also going to try it with an Index (but I suspect that with small
number of record inserts that might have the opposite effect.)

Yup. No good for small amount of data and with IN statament there is
normally small amount.
As another benefit (your technique) is that you don't need to pay attention
to number or parameters within IN list (is it 255 normally?).

I'm going to

play with it tomorrow and I'll post a prologue.

Great. Anyway I already like the approach even if it causes some performance
penalities.

 

[Bernie Yaeger]

Hi Bill,

I ran into this issue long ago and came up with what I think is a very
useful workaround (which I constantly employ): the inability to pass an
array to an sp was the impetus for this: I create an sp 'on the fly' - my
own form of dynamic sql - and pass a string that represents the array into
the sql select. Both the size of an sp in SQL 2000 and the string size make
this almost fullproof - you would need an array with hundreds of thousands
of elements and an sp the size of the Grand Canyon to make this fail.
Here's the code:

longstring = "('"

For j = 0 To i - 1

longstring = longstring & RTrim(titlearray(j)) & RTrim(issuecodearray(j)) &
Chr(39) & ",'"

Next

longstring = Mid(longstring, 1, longstring.Length - 2)

longstring = longstring & ")"

Dim docmd As New SqlCommand

docmd = New SqlCommand("exec sp_dropgensum", oconn)

Try

docmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

docmd = New SqlCommand("exec sp_dropsp_copyintogensum", oconn)

Try

docmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_copyintogensum AS " _

& "select * into gensum from invdet where substring(imcacct,1,5) = '30544'
and rtrim(title) + rtrim(issuecode) in " & longstring

Dim sqladaptdel As New SqlDataAdapter

sqladaptdel.SelectCommand = New SqlCommand(creationstring, oconn)

'this creates the new sp

Try

sqladaptdel.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

' then i run the new sp

Dim addcmd As New SqlCommand

addcmd = New SqlCommand("sp_copyintogensum", oconn)

addcmd.CommandType = CommandType.StoredProcedure

Try

addcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Let me know what you think.

Bernie Yaeger

 

[Ross Donald]

Hi Bill,

You can use parameters with the IN statement. Here is an example.

-- Northwind
exec sp_executesql N'SELECT [Orders].* FROM [Orders]
WHERE [Orders].[OrderID] IN (@OrderID1, @OrderID2, @OrderID3)'
, N'@OrderID1 int, @OrderID2 int, @OrderID3 int'
, @OrderID1 = 10253
, @OrderID2 = 10260
, @OrderID3 = 10265

--
Ross Donald
http://www.radsoftware.com.au


| I hate using Dynamic SQL and was prohibited from doing so b/c we couldn't
| use 'IN' Statement in the where clause with Parameters which broke out
data
| access block. I wrote a work around which works, but I did it in haste.
| I'd really like some feedback (be harsh, I'm not thin skinned) and would
| like to improve it. Here's the link..
| http://www.knowdotnet.com/articles/temptables.html It's based on a
VARCHAR
| field, but could easily be enhanced with another overload. Any feedback
| would be appreciated.
|
| Thanks,
|
| Bill
|
| --
| Cordially,
|
| W.G. Ryan
| (e-mail address removed)
| www.devbuzz.com
| www.knowdotnet.com
|
|