Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

In clause in SQL Query

Posted on 2004-11-19
8
Medium Priority
?
1,280 Views
Last Modified: 2007-12-19
Hi,
In the below two queries, first one returns 2 records and the second one returns no records. How do I make the second query work?

--Query 1
Select * from MediaOwners where MediaOwnerName in ('Viacom','Lamar')

--Query 2
declare @x varchar(100)
set @x='''Viacom'',''Lamar'''
print @x
Select * from MediaOwners where MediaOwnerName in (@x)
0
Comment
Question by:Karuppaiah
8 Comments
 
LVL 6

Accepted Solution

by:
robertjbarker earned 150 total points
ID: 12632511
Instead of seaching for 'Viacom' or 'Lamar' you are searching for the single string 'Viacom','Lamar'. One way to solve the problem is put the individual strings in a temp table:


declare @x varchar(100)
create table #searchlist (item varchar(100))
insert #searchlist (item) values ('Viacom')
insert #searchlist (item) values ('Lamar')
Select * from MediaOwners where MediaOwnerName in (select * from #searchlist)
drop table #searchlist
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 12632532
Another way:

declare @x varchar(100)
set @x='''Viacom'',''Lamar'''

declare @xs varchar(1000)
set @xs = 'Select * from MediaOwners where MediaOwnerName in (' + @x +')'
exec (@xs)
0
 
LVL 12

Expert Comment

by:ill
ID: 12633154
-- one more possibility, if @x members are unique
declare @x varchar(100)
set @x='''Viacom'',''Lamar'''
print @x
Select * from MediaOwners where @x like ( '%'''+ MediaOwnerName + '''% ')
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12633163
or of course parse the inlist string into separate components
(if you'll allow only a fixed number) and

select *
from ..
where yyy in (@x1.@x2,@x3,...),
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12633407
How about something like (bear with the typos, I haven't got SQL Server on this machine)

--First create a new (reusable UDF) to split the string up.
CREATE FUNCTION dbo.fnSplitString(@MyString varchar(8000),@delimiter char(1))
RETURNS @SplitString TABLE
(
    string varchar(8000)
)
AS
BEGIN
    DECLARE @chindex int
    @chindex = CHARINDEX(@delimiter,@MyString)
    WHILE @chindex > 0
    BEGIN
        INSERT INTO @SplitString
        VALUES(LEFT(@MyString,@chIndex -1))
        SET @MyString = SUBSTRING(@MyString,chIndex+1,8000)
    END
    INSERT INTO @SplitString
    VALUES(@MyString)
    RETURN
END

-- Then alter your original query thus:
declare @x varchar(100)
set @x='Viacom,Lamar'
print @x
Select * from MediaOwners where MediaOwnerName in (SELECT string FROM dbo.fnSplitString(@x), ",")


0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12633415
Sorry, that last line should have read:

Select * from MediaOwners where MediaOwnerName in (SELECT string FROM dbo.fnSplitString(@x.','))
0
 
LVL 5

Expert Comment

by:volking
ID: 12634423
hmmmm ... wouldn't this work ... just build a TSQL string composed with the appropriate quotes and execute?

declare @MySqlRaw varchar(1000)
SET @MySqlRaw = 'Select * from MediaOwners where MediaOwnerName in (' + ''''Viacom'',''Lamar''' + ')'
EXEC (@MySqlRaw)

(p.s. not sure I have the single quotes balanced in above but you get the idea)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12636065
>>wouldn't this work<<
Yes, but it requires using dynamic SQL.  This implies bad performance and lousy security (you must give the user Select permissions for the table).
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question