Using WHERE IN(...)
-
I am trying to select records based upon User Roles. I want to pass a string as a parameter to my stored procedure. The parameter would contain something like this: ('role1', 'role2', 'role3', ......). This returns zero records. If I use something like this (role1), it does return records.
Any help would be appreciated....
Dan
ALTER PROCEDURE
dbo.treeViewDefaultDir
@ListOwner
varchar(200),
@Roles
varchar(2000)
AS
SELECT Distinct ListID,
ListTitle,
ListParent,
ListOwner
FROM GroupList
WHERE (ListID IN
(
SELECT ListReferencedID
FROM GroupListShares
WHERE (SecurityGroupReferenceID
IN(@Roles))
OR ListOwner = @ListOwner
)
)
ORDER BY ListTitle
RETURN
-
I would do the following, create a sql string then execute that once it is built - example:
create PROCEDURE dbo.test
@ListOwner varchar(200),
@Roles varchar(2000)
AS
/* create string buffer in procedure that builds sql command */
declare @sqlstr nvarchar(1000)
/* display to see what I am passing in */
print @Roles
set @sqlstr = N'SELECT ListReferencedID FROM GroupListShares '
set @sqlstr = @sqlstr + N'WHERE (SecurityGroupReferenceID IN (' +@Roles + ')) '
/* show what the command now looks like */
print @sqlstr
/* now eexecute the command */
EXECUTE sp_executesql @sqlstr
RETURN
......................
Hope this helps - may not be the ideal way - but it does work
you would call the SP as follows:
exec test 'arg1',"'arg2','arg3','arg4'"
oh make sure your variables are long enough - I just did mine to make sure it worked - so make yours like nvarchar(4000) if you need
** What you see, Depends on what you Thought, Before, You looked! **
-
Something like the below should work and be pretty efficient. Obviously i havnt tested this so use at your own risk.
ALTER PROCEDURE dbo.treeViewDefaultDir
@ListOwner varchar(200),
@Roles varchar(2000)
AS
if object_id('tempdb.dbo.#tmp') is not null
drop table #tmp
create table #tmp
(
userrole int
)
declare @sql nvarchar(4000)
select @sql = 'insert #tmp select cast('''+
replace(@Roles, ',', ''' as int) union all select cast(''' )+''' as int)'
print @sql
exec sp_executesql @sql, N'@Role nvarchar(4000)', @Role
SELECT Distinct ListID,
ListTitle,
ListParent,
ListOwner
FROM GroupList gl
join GroupListShares gls
on gl.ListID = gls.ListReferencedID
left join #tmp t
on t.userrole = g.SecurityGroupReferenceID
where (t.userrole is not null
or @ListOwner = gls.ListOwner)
ORDER BY ListTitle
RETURN
-
Aside from resorting to dynamic SQL (and all the worries that go with it), you may want to make a UDF that returns a table from a delimited string:
WHERE (SecurityGroupReferenceID IN (Select [Value] from dbo.udfConvertDelimitedToVarchar(@Roles))) OR ListOwner = @ListOwner
or you can fall back on the old trick of:
WHERE (PATINDEX(('%,' + SecurityGroupReferenceID + ',%'), (','+@Roles+',')) > 0) OR ListOwner = @ListOwner(To avoid needless complications, I'm assuming the string is in the form of 'role1,role2,role3' without any spaces or internal quotes)
-
That's why I didn't presume it was an int.
-
works for me - just dont put in the ' ' when capturing the variables in your string list - just tested it and it went through like a charm . btw he did mention that he was passing in as a string in the original post
** What you see, Depends on what you Thought, Before, You looked! **
-
This
WHERE (PATINDEX(('%,' + SecurityGroupReferenceID + ',%'), (','+@Roles+',')) > 0)
almost works for me. Now I have narrowed down the problem to the individual roles I am passing. These are actual user roles and the contain "\\" in each name. Such as "KANSASCOMMERCE\\Admininstration" If I run the stored procedure and manually input the parameter, I get the correct results. But when I pass these as a parameter, I get zero results.
any suggestions.........thanks dan
-
Could it be the extra N in 'KANSASCOMMERCE\\Admininstration'?
I don't think you need to escape slashes.
(Though, an underscore in the pattern should probably be replaced with [_])
WHERE (PATINDEX(('%,' + REPLACE(SecurityGroupReferenceID,'_','[_]') + ',%'), (','+@Roles+',')) > 0)Whichever way you store the values, that's the way you should pass them to the stored procedure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply