sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-11 : 04:48:28

declare @UserID varchar(100)
set @UserID = 'Gb0154'

select UserID from tblUserFiles where UserID IN (@UserID)

How can this query work if there are two users, i.e. 'Gb0154' , 'gb1234'

I do NOT want to build a sql string to do a sp_sqlexec.
I have tried :
set @UserID = '''Gb0154'',''gb123'''

THanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-11 : 05:01:35

declare @UserID varchar(100)
set @UserID = 'Gb0154,gb1234'
select UserID from tblUserFiles where ',' + @UserID + ',' like '%,' + UserID + ',%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-11 : 05:07:29

hi,
What if the UserID can be more than 2, i.e: 10, 50, etc...
Thanks

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-11 : 06:44:48

It still works
Try Nigels solution in Northwind

Andy

Beauty is in the eyes of the beerholder

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-11 : 22:50:33

This will work for as many users as you want.


declare @Users table 
( UserID varchar(100) not null primary key clustered )

insert into @Users (UserID)
select 'Gb0154'  union all
select 'gb1231'  union all
select 'gb1232'  union all
select 'gb1233'  union all
select 'gb1234'  union all
select 'gb1235'  union all
select 'gb1236'

select
	a.UserID
from
	tblUserFiles a
	join
	@Users b
	on ( a.UserID = b.UserID )
order by
	a.UserID

quote:
Originally posted by fmardani

declare @UserID varchar(100)
set @UserID = 'Gb0154'

select UserID from tblUserFiles where UserID IN (@UserID)

How can this query work if there are two users, i.e. 'Gb0154' , 'gb1234'

I do NOT want to build a sql string to do a sp_sqlexec.
I have tried :
set @UserID = '''Gb0154'',''gb123'''

THanks

Codo Ergo Sum

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-12 : 15:31:20

>>> select UserID from tblUserFiles where ',' + @UserID + ',' like '%,' + UserID + ',%' <<<

Does this work if the UserIDs could contain an embedded comma?

.

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 15:43:59

If a userID can contain a comma then you can't use a csv string so the question is meaningless.
In that case you would either use another delimitter like | or ^ or quote delimit all the values then the query would become
select UserID from tblUserFiles where ',' + @UserID + ',' like '%,"' + UserID + '",%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-12 : 15:53:57

Hi nr,
Your query works very well for my purpose.
Can you please explain what is exactly happening in this query?
Thanks
select UserID from tblUserFiles where ',' + @UserID + ',' like '%,"' + UserID + '",%'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 19:45:44

if @userID is say '123'
then ',' + @UserID + ',' becomes ',123,'

if in one row in the table you have 123,456,789
then '%,' + UserID + ',%' becomes '%,123,456,789,%' for that row.

with the like operator you compare ',123,' (',' + @UserID + ',' from above)
with '%,123,456,789,%' and that results in true for ',123,' because it finds it at the begining.

another example with same functionality:
where 'this is my place' like '%my%'
returns true an so that row is selected.

is that clear enough?

Go with the flow & have fun! Else fight the flow

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-12 : 19:52:18

quote:
Originally posted by spirit1

is that clear enough?

As mud!

Beauty is in the eyes of the beerholder

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 19:53:50

so i guess not, huh?

Go with the flow & have fun! Else fight the flow

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-12 : 20:10:03

Thanks for the nice explanation. Helps alot ;-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 20:30:32

ok i just saw i made a little mistake in the example:
@UserID should be '123,456,789' and it goes into ',123,456,789,'
and UserID should be '123' and it goes into '%,123,%'

otherwise the concept is the same.

Go with the flow & have fun! Else fight the flow

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-14 : 05:55:57

Hi,
Do you see why my query does not return any records please?
Basically I would like to pass either one UserID or many UserIDs
I have reduced the lines of code to make it simpler for you to see.
Thanks

declare @UserID nvarchar(200)
declare @ValueCentreID int

set @UserID = 'GBBAUA,GB123'

SELECT U.Name UserName, VC.ValueCentre
FROM
tblUser U, tblValueCentre
WHERE
U.ValueCentreID = VC.ValueCentreID
AND ',' + @UserID + ',' like '%,' + u.UserID + ',%'
UNION
SELECT U.Name UserName, VC.ValueCentre
FROM
tblUser U, tblValueCentre VC
WHERE
U.FileCollectImported > '2005-01-18'
AND U.ValueCentreID = VC.ValueCentreID
AND ',' + @UserID + ',' like '%,' + u.UserID + ',%'

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-14 : 06:34:08

got it,
this line should be the first thing in the where clause
',' + @UserID + ',' like '%,' + u.UserID + ',%'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-14 : 07:12:48

i would strongly suggest you use join instead of connecting tables in the where clause.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources