Dynamic Where clause

Hi
I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
clause
e.g
Where Value = myArray(0) or myArray(1)...
But the where clause can not be explicit because the length of the array is
variable
What is a professional way to do something like this?
ThksAre you just using T-SQL or are you using VB in the front end?
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:18A39651-A7B4-4208-9C52-515BC1198955@.microsoft.com...
> Hi
> I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
> clause
> e.g
> Where Value = myArray(0) or myArray(1)...
> But the where clause can not be explicit because the length of the array
> is
> variable
> What is a professional way to do something like this?
> Thks
>|||Check out this article on ASPFAQ to get started:
http://www.aspfaq.com/show.asp?id=2248
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:18A39651-A7B4-4208-9C52-515BC1198955@.microsoft.com...
> Hi
> I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
> clause
> e.g
> Where Value = myArray(0) or myArray(1)...
> But the where clause can not be explicit because the length of the array
> is
> variable
> What is a professional way to do something like this?
> Thks
>|||>> What is a professional way to do something like this? <<
Put the values in the only data structure allowed in SQL, a table
WHERE x IN (SELECT x FROM Parmlist
)|||Yeah the code is in VB, is not transac but I would like to know how to make
it too.|||On the VB.NET side, create the SQL Statement dynamically using a String
variable and a For Loop:
Dim sqlstr As String
sqlstr = "SELECT * FROM table1 WHERE col1 IN ("
For i = 0 To MyArray.Length - 1
sqlstr += CType(MyArray(i), String)
If i < MyArray.Length - 1 Then
sqlstr += ","
End If
Next
sqlstr += ")"
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:5897F0E6-FBE8-4BF4-97B4-DBD5A943DDB4@.microsoft.com...
> Yeah the code is in VB, is not transac but I would like to know how to
> make
> it too.|||pretty good thks
--
Kenny M.
"Michael C#" wrote:

> On the VB.NET side, create the SQL Statement dynamically using a String
> variable and a For Loop:
> Dim sqlstr As String
> sqlstr = "SELECT * FROM table1 WHERE col1 IN ("
> For i = 0 To MyArray.Length - 1
> sqlstr += CType(MyArray(i), String)
> If i < MyArray.Length - 1 Then
> sqlstr += ","
> End If
> Next
> sqlstr += ")"
>
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:5897F0E6-FBE8-4BF4-97B4-DBD5A943DDB4@.microsoft.com...
>
>

Dynamic SQL Query

I have an array list of Branch_ID's; i need to select all records from 2 tables that have any of the branch id's in that array associated with them. How would I go about doing this?

Well in Dynamic SQL you could simply construct the query out ofsubstrings (create the appropriate IN (a, b, c,d) clause) but if youwanted to use a stored procedure you should take a look here:[http://odetocode.com/Articles/365.aspx
I use a similar technique to retrieve a number of records from a table based on a csv list being passed in. Works great.
|||To side-step dynamic SQL consider using XML and OpenXML to create a virtual table to join against. This way you can use Text without having to worry about tricky text manipulation and you don't have to worry about your array being too big to contain in a varchar. It's not perfect for performance but prob not far from dynamic SQL and without all those nasty injection attack worries.|||

Pass the select statement to the stored procedure as a parameter. Try something like this.
Create procedure yourProc

@.select_statement as varchar(200)

As

Declare @.sql as varchar(500)
Set @.sql = 'Select' + @.select_statement +
'FROM table1, table2'
EXEC(@.sql)