Using parameter values in an IN clause
-
I've been struggling with how to get SRS to let me pass a string value as a parameter into an IN clause and I'm stumped.
I've got the following simple scenario:
SELECT DisplayName, LastName, Department FROM tblEmployee WHERE (UserName IN (@usernames))
And am trying to pass a few usernames into the parameter for the report. Passing a single username such as asmith works fine, but if I go with multiple values I get no results, for example the following fails: asmith,bjones.
I've tried a number of different iterations of what I feed the parameter:
asmith,bjones
'asmith','bjones'
asmith','bjones (thinking that perhaps the leading and trailing quotes were being added for me)
All with no luck. Does anyone know if this will work and what I'm missing? Thanks for any help =)
/Kevin
-
Unfortunately for you, you are doing nothing incorrect. At this time (haven't tried with SQL 2K5) you can't get there from here. The IN clause doesn't know how to interpret the @var as anything except a literal.
THe only way that I know of to get around this is to build dynamic SQL and use either EXEC @SQL or EXEC sp_executesql @SQL to get what you want...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
yep, yep, yep
Use a temporary table variable
use pubs
set nocount on
declare @Usernames varchar(5000)
set @Usernames = 'me,him,she,them,Karin'
declare @TblUserNames TABLE
(UserNamesId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
UserName varchar(128) not NULL)
-- snipped from tcartwright he as a function to do this
-- http://www.sqlservercentral.com/scripts/contributions/592.asp
DECLARE @Delimiter2 varchar(12),@Delimiter varchar(12),
@UserName varchar(128),
@iPos int,
@DelimWidth int
--had to do this cuz if they send in a > 9 char delimiter I could not pre and post append the % wildcards
SET @Delimiter2 = ','
SET @Delimiter2 = ISNULL(@Delimiter2, ',')
SET @DelimWidth = LEN(@Delimiter2)
IF RIGHT(RTRIM(@UserNames), 1) <> @Delimiter2
SELECT @UserNames = RTRIM(@UserNames) + @Delimiter2
IF LEFT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = '%' + @Delimiter2
IF RIGHT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = @Delimiter2 + '%'
SELECT @iPos = PATINDEX(@Delimiter2, @UserNames)
WHILE @iPos > 0
BEGIN
SELECT @UserName = LTRIM(RTRIM(LEFT(@UserNames, @iPos - 1)))
IF @@ERROR <> 0 BREAK
SELECT @UserNames = RIGHT(@UserNames, LEN(@UserNames) - (LEN(@UserName) + @DelimWidth))
IF @@ERROR <> 0 BREAK
INSERT INTO @TblUserNames VALUES(@UserName)
IF @@ERROR <> 0 BREAK
SELECT @iPos = PATINDEX(@Delimiter2, @UserNames)
IF @@ERROR <> 0 BREAK
END
-- select * from @TblUserNames
SELECT emp_id, LName, hire_date
FROM Employee E
inner join @TblUserNames U
on E.fname = U.UserName
With tcartwright's function
( http://www.sqlservercentral.com/scripts/contributions/592.asp)
This would even be more easy
select e.*
from pubs..employee E
inner join dbo.fnSplit('me,him,she,them,Karin',',') U
on E.fname = U.item
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Try this
The UDF is one I use alot, as alot of "old" db's use string values with a delim. ....
This is also an intro into @variableTables, one of teh coolest things with sql server 2000. Check out this article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q315968
declare @s-2 varchar(128)
select @s-2 = "PC1035,PS1372,BU1111,PS7777"
select * from titles where title_id IN (select item from
dbo.fnc_10_comma_delimited_char (@s , ',')
------------------HERE IS THE UDF
if exists (select * from sysobjects where id = object_id('dbo.fnc_10_comma_delimited_char') and xtype = 'TF')
drop function dbo.fnc_10_comma_delimited_char
GO
CREATE FUNCTION dbo.fnc_10_comma_delimited_char(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
RETURNS @tablevalues TABLE
(item varchar(8000))
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = @p_Item
END
RETURN
END
GO
--GRANT EXECUTE ON fnc_10_comma_delimited_char TO someUser
For Kicks, here is the "int" version
if exists (select * from sysobjects where id = object_id('dbo.fnc_10_comma_delimited_int') and xtype = 'TF')
drop function dbo.fnc_10_comma_delimited_int
GO
CREATE FUNCTION dbo.fnc_10_comma_delimited_int(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
RETURNS @tablevalues TABLE
(item int)
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = convert(int,@p_Item)
END
RETURN
END
GO
--GRANT EXECUTE ON fnc_10_comma_delimited_int TO someUser
-
Hi all,
I also had a problem with using var in "IN" clause...
but for case that you describing you can try something like this
select * from sometable
where userid in (select userid from userslisttable
where CHARINDEX(','+userlisttable.userid+',', ','+@parameter+',')>0 )
Hope, it helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply