SQL Parameters using the IN keyword

  • Thread starter Thread starter Ben
  • Start date Start date
B

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 SelectionChanged
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
 
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.
 
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 misunderstanding, 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."
 
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.
Click to expand...

Just be aware of possibility of SQL injection attack :-)
 
Just be aware of possibility of SQL injection attack :-)
Click to expand...

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 said:
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.
Click to expand...

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

Jon Skeet said:
Whoops - meant to say "insert the parameter list dynamically as well".
Click to expand...

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

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?
 
Hi Jon,

Jon Skeet said:
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?
Click to expand...

Yes, it applies, trust me, Sql server loves limitations :-)
I will never understand why in the world they don't implement array
parameters.
 
First of all, it seems your original SQL can be simplified a bit to:
SELECT Products.Name AS Product,
SUM(Summary.Owned) AS Owned,
SUM(Summary.Maintained) AS Maintained,
SUM(Summary.Unlocked) AS Unlocked,
SUM(Summary.Vouchers) 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
 
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.
Click to expand...

In addition to the Dynamic SQL suggestions, I have 2 other options.

1) Put the codes in a temp table and join to it. This isn't usually
pratical in cases where you want to pass the values in a parameter,
but in other cases where you need to do something similar based
on other values in a stored procedure it can come in handy.

2) For this case where you want to pass the value list in as a single
parameter. I forgot where I picked up this trick, or I'd give them
the credit. It's been a big help to me. Use the CHARINDEX function
and make sure the value list begins and ends with a comma.

For example:

DECLARE @codes NVARCHAR(100)
SET @codes = 'G01,G02'

SELECT * FROM tbRules
WHERE CHARINDEX(rule_code, @codes) > 0

returns the same result set as

SELECT * FROM tbRules
WHERE rule_code IN ('G01', 'G02')
 
You can ignore the part in my previous reply where I said to make sure
the list begins and ends with a comma. That isn't true (and my
example doesn't show it anyway). I have to do that in a special case
in my code, but that is specific to my situation. You don't need the
extra commas.
 
Hi,

Thanks for all your responces. Firstly bob, CHARINDEX doesn't seem to
work. Can't find it in my 'Complete Reference SQL' book, nor does
SQL2005 accept the keyword. :/ Is there something else I have to do?

Secondly, James (Curran), the values I want to use as parameters are
coming from the selected rows in a datagridview. So originally they are
coming from another table, but I can't think of a way to link the
selected rows to the database.

I think I need to just dynamically create the SQL statement, which is
fine, just wanted to make sure there was no way of using the generated
ones first.

Thanks!
 
Thanks, Miha. Actually I've already aware of that. But I couldn't find
other ways to complete the IN clause. Do you have any suggestions?

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

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

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