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

[Bob]

be**@pronamics. com.au (Ben) wrote in message news:<a3******* *************** ****@posting.go ogle.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.

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')

[Bob]

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.

[Ben Townsley]

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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Kevin Yu [MSFT]]

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."

[Kevin Yu [MSFT]]

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."

[Miha Markic [MVP C#]]

Hi Kevin,

Here you go:
Passing a list/array to an SQL Server stored procedure
http://vyaskn.tripod.com/passing_arr...procedures.htm

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

"Kevin Yu [MSFT]" <v-****@online.mic rosoft.com> wrote in message
news:Ne******** ******@cpmsftng xa10.phx.gbl...

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."