SQL Parameters using the IN keyword

[Ben]

Hey,

Not sure if this is the right group to post this on, so sorry in
advance if it isn't.

I'm using the VS.NET 2005 beta, and am trying to fill one grid, based
on what is selected in the other grid. So, in the SelectionChange d
event, I get the IDs of the selected rows in the first grid, and..
this is where i get stuck :)

This is my statement:

SELECT (SELECT Products.Name FROM Products WHERE Products.ID =
Summary.Product ) AS Product, SUM(Owned) AS Owned, SUM(Maintained) AS
Maintained, SUM(Unlocked) AS Unlocked,
SUM(Vouchers) AS Vouchers
FROM dbo.Summary
WHERE Summary.Office IN (SELECT Office.ID FROM Office WHERE
Office.Company IN ( @Parameter))
GROUP BY Product
ORDER BY Product

What I would like to be able to do, is replace @Parameter, with
several comma seperated Guid values. However, no matter what I do, if
i try to add a parameter, it gets interpreted as one value, and thus
has some difficulty interpretting something two Guids seperated by a
comma.

Is there a way around this? Or do i have to manually code the this to
use an actual SQL statement rather than trying to use parameters?

Any help would be most appreciated, and I hope I've included enough
detail.

Thanks!

Ben

[Kevin Yu [MSFT]]

Hi Ben,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to join some values in the IN
keyword of a SQL statement. If there is any misunderstandin g, please feel
free to let me know.

As far as I know, since we're joining the two strings together, we cannot
use a parameter to do so. The best workaround, I think, is to use a Text
command instead of a stored procedure. We can join the string together in
code and then execute it.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Miha Markic [MVP C#]]

Hi Ben,

Yep, this is a chronic problem with databases.
The easiest way is to create sql statement dynamically.
Beware of sql injection possibility - you have to check parameters!
Another solution is to pass a string with Id separated with a char or
something and do the processing on the server.
This is more tedious but you don't have problem with sql injection if done
properly.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

"Ben" <be**@pronamics .com.au> wrote in message
news:a3******** *************** ***@posting.goo gle.com...

Hey,

Not sure if this is the right group to post this on, so sorry in
advance if it isn't.

I'm using the VS.NET 2005 beta, and am trying to fill one grid, based
on what is selected in the other grid. So, in the SelectionChange d
event, I get the IDs of the selected rows in the first grid, and..
this is where i get stuck :)

This is my statement:

SELECT (SELECT Products.Name FROM Products WHERE Products.ID =
Summary.Product ) AS Product, SUM(Owned) AS Owned, SUM(Maintained) AS
Maintained, SUM(Unlocked) AS Unlocked,
SUM(Vouchers) AS Vouchers
FROM dbo.Summary
WHERE Summary.Office IN (SELECT Office.ID FROM Office WHERE
Office.Company IN ( @Parameter))
GROUP BY Product
ORDER BY Product

What I would like to be able to do, is replace @Parameter, with
several comma seperated Guid values. However, no matter what I do, if
i try to add a parameter, it gets interpreted as one value, and thus
has some difficulty interpretting something two Guids seperated by a
comma.

Is there a way around this? Or do i have to manually code the this to
use an actual SQL statement rather than trying to use parameters?

Any help would be most appreciated, and I hope I've included enough
detail.

Thanks!

Ben

[Miha Markic [MVP C#]]

As far as I know, since we're joining the two strings together, we cannot
use a parameter to do so. The best workaround, I think, is to use a Text
command instead of a stored procedure. We can join the string together in
code and then execute it.

Just be aware of possibility of SQL injection attack :-)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

[Jon Skeet [C# MVP]]

<"Miha Markic [MVP C#]" <miha at rthand com>> wrote:

As far as I know, since we're joining the two strings together, we cannot
use a parameter to do so. The best workaround, I think, is to use a Text
command instead of a stored procedure. We can join the string together in
code and then execute it.

Just be aware of possibility of SQL injection attack :-)

To avoid SQL injection, you can build the SQL up dynamically, but still
only use parameters instead of any direct values - insert the values
dynamically as well, basically.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

Jon Skeet [C# MVP] <sk***@pobox.co m> wrote:

Just be aware of possibility of SQL injection attack :-)

To avoid SQL injection, you can build the SQL up dynamically, but still
only use parameters instead of any direct values - insert the values
dynamically as well, basically.

Whoops - meant to say "insert the parameter list dynamically as well".

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Miha Markic [MVP C#]]

Hi Jon,

"Jon Skeet [C# MVP]" <sk***@pobox.co m> wrote in message
news:MP******** *************** *@msnews.micros oft.com...

Jon Skeet [C# MVP] <sk***@pobox.co m> wrote:

> Just be aware of possibility of SQL injection attack :-)

To avoid SQL injection, you can build the SQL up dynamically, but still
only use parameters instead of any direct values - insert the values
dynamically as well, basically.

Whoops - meant to say "insert the parameter list dynamically as well".

Yes. The only problem I see is that you are limited to 2100 parameters :-)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

[Jon Skeet [C# MVP]]

<"Miha Markic [MVP C#]" <miha at rthand com>> wrote:

Whoops - meant to say "insert the parameter list dynamically as well".

Yes. The only problem I see is that you are limited to 2100 parameters :-)

Ooh, I didn't know that. Is it documented anywhere? I see that it's
documented for SQL server for stored procedures, but does that also
apply to parameterised text queries?

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Miha Markic [MVP C#]]

Hi Jon,

"Jon Skeet [C# MVP]" <sk***@pobox.co m> wrote in message
news:MP******** *************** *@msnews.micros oft.com...

<"Miha Markic [MVP C#]" <miha at rthand com>> wrote:

> Whoops - meant to say "insert the parameter list dynamically as well".

Yes. The only problem I see is that you are limited to 2100 parameters
:-)

Ooh, I didn't know that. Is it documented anywhere? I see that it's
documented for SQL server for stored procedures, but does that also
apply to parameterised text queries?

Yes, it applies, trust me, Sql server loves limitations :-)
I will never understand why in the world they don't implement array
parameters.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

[James Curran]

First of all, it seems your original SQL can be simplified a bit to:
SELECT Products.Name AS Product,
SUM(Summary.Own ed) AS Owned,
SUM(Summary.Mai ntained) AS Maintained,
SUM(Summary.Unl ocked) AS Unlocked,
SUM(Summary.Vou chers) AS Vouchers
FROM
dbo.Summary
inner join Products on Products.ID = Summary.Product
inner join Office on Office.ID = Summary.Office
where Office.Company IN ( @Parameter)
GROUP BY Product
ORDER BY Product

But that doesn't help your problem. However, from your message it seems
that the values are Guids. This is helpful is two ways. First of all, I
really doubt you are asking your users to type in Guids, which pretty much
alleviates SQL injection problem. If you control where the value is coming
from, you can know it isn't going to hack you. Which brings us to the next
question: Then, where does that list come from? If it's coming from the DB,
you might as well just make it another join on the query.

--
Truth,
James Curran
[erstwhile VC++ MVP]
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
"Ben" <be**@pronamics .com.au> wrote in message
news:a3******** *************** ***@posting.goo gle.com...

Hey,

Not sure if this is the right group to post this on, so sorry in
advance if it isn't.

I'm using the VS.NET 2005 beta, and am trying to fill one grid, based
on what is selected in the other grid. So, in the SelectionChange d
event, I get the IDs of the selected rows in the first grid, and..
this is where i get stuck :)

This is my statement:

SELECT (SELECT Products.Name FROM Products WHERE Products.ID =
Summary.Product ) AS Product, SUM(Owned) AS Owned, SUM(Maintained) AS
Maintained, SUM(Unlocked) AS Unlocked,
SUM(Vouchers) AS Vouchers
FROM dbo.Summary
WHERE Summary.Office IN (SELECT Office.ID FROM Office WHERE
Office.Company IN ( @Parameter))
GROUP BY Product
ORDER BY Product

What I would like to be able to do, is replace @Parameter, with
several comma seperated Guid values. However, no matter what I do, if
i try to add a parameter, it gets interpreted as one value, and thus
has some difficulty interpretting something two Guids seperated by a
comma.

Is there a way around this? Or do i have to manually code the this to
use an actual SQL statement rather than trying to use parameters?

Any help would be most appreciated, and I hope I've included enough
detail.