Using 'IN' in a 'SELECT' statement

[whateveragain]

What is wrong with this code? I'm passing in a string of states separated by a space. This works if I only pass in one state. It doesn't work if I pass in several states separated with a space. I was trying to avoid parsing @PSelstate. Thanks

ALTER procedure [dbo].[update_subcnty]
-- Add the parameters for the stored procedure here
@PSelstate char(400)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT *
into subcnty
FROM County
where County.state IN (@PSelstate)
ORDER BY County.State, County.County ;

END

 

[checkai]

You'll have to do dynamic sql or utilize a function that will send back a table...this isn't supported.

 

[bborissov]

What you did is to seek County.state in a single string. The parameter is just a string it have NO separated values in it.

You could search for Split function here or check this article: Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands:

http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[genomon]

Subquery - will this work for the FROM clause?

FROM County
where County.state IN (SELECT state FROM county WHERE state = @PSelstate)

Cogito eggo sum – I think, therefore I am a waffle.

 

[bborissov]

No.
The variable is variable and it contains ALL States you want in it as a SINGLE string.
No matter what you use you NEED to split that variable into table first.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

No matter what you use you NEED to split that variable into table first.

Really?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

If you pass only ONE value, then yes, you don't need it

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

No.

What I'm referring to is.... if you have a comma separated list (with multiple parts), there is a way to use it without using dynamic SQL or a split function.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This method may not perform well on large tables, but if you have a relatively small table (think lookup table here), this is likely to perform good enough.

ALTER procedure [dbo].[update_subcnty]
    -- Add the parameters for the stored procedure here
    @PSelstate char(400)
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

SELECT *
into subcnty
FROM County
[!]where ' ' + @PSelstate + ' ' Like '% ' + County.state + ' %'[/!]
ORDER BY County.State, County.County ;

END

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

How?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

This should work (space separated values) but would cause a table scan, so should only be attempted on small tables, as George said - interesting that I never saw this solution suggested, though a similar idea used in How to search for all words inclusive without using Full Text search.

 

[bborissov]

O sorry George, when I posted my "How?" I didn't saw your answer (BTW I should drink my ginkgo biloba )
BTW we speak about comma separated values, shouldn't the where clause be:

where ','+@PSelstate+',' Like '%,' + County.state + ',%'

BTW I still prefer Split function

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

I talked about comma separated, but then noticed the original question mentioned space separated.

I'm passing in a string of states separated by a space.

BTW I still prefer Split function

Me too!

But let's not forget that sometimes the "inferior" method is perfectly acceptable too. If you use this method against a small lookup table, and then join to your main table, this method may even be the preferred one. A split function involves loops, this method involves a scan (table or index) in the execution plan. Which is better? Hard to say, could be either one.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom