May 27, 2010 at 3:58 pm
This shouldnt matter when you test it : WHERE LoweredRoleName NOT IN('expired','admin')
DECLARE @list VARCHAR(1000)
SET @list = (SELECT STUFF((SELECT ',' + CHAR(39) +LoweredRoleName + CHAR(39)
FROM dbo.aspnet_Roles
WHERE LoweredRoleName NOT IN('expired','admin')
FOR XML PATH('')),1, 1, '') AS RolesCSV)
SELECT @list
SELECT
A.LoweredEmail,
D.RoleName
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_Users AS B WITH (NOLOCK) ON A.UserId = B.UserId
INNER JOIN dbo.aspnet_UsersInRoles AS C WITH (NOLOCK) ON B.UserId = C.UserId
INNER JOIN dbo.aspnet_Roles AS D WITH (NOLOCK) ON C.RoleId = D.RoleId
WHERE D.RoleName IN (@list)
go
Hi,
I get no result when I do it this way
WHERE D.RoleName IN (@list)
BUt I get the a result when I put in manually like : 'role1','role2','role3'
How can I get this to work?:cool:
NOTE: you need the aspnetdb membership database for this to work.
May 27, 2010 at 4:35 pm
AFAIK, the only way to get this to work is to use dynamic SQL (code snippet untested...).
DECLARE @sql NVARCHAR(4000)
SET @sql='
SELECT
A.LoweredEmail,
D.RoleName
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_Users AS B WITH (NOLOCK) ON A.UserId = B.UserId
INNER JOIN dbo.aspnet_UsersInRoles AS C WITH (NOLOCK) ON B.UserId = C.UserId
INNER JOIN dbo.aspnet_Roles AS D WITH (NOLOCK) ON C.RoleId = D.RoleId
WHERE D.RoleName IN ('+@list+')'
EXEC(@sql)
May 27, 2010 at 4:50 pm
Thanks:-)
May 27, 2010 at 5:05 pm
Thinking about it, you probably can avoid the dynamic SQL part if you'd replace your variable with the underlying subselect (again, untested):
SELECT
A.LoweredEmail,
D.RoleName
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_Users AS B WITH (NOLOCK) ON A.UserId = B.UserId
INNER JOIN dbo.aspnet_UsersInRoles AS C WITH (NOLOCK) ON B.UserId = C.UserId
INNER JOIN dbo.aspnet_Roles AS D WITH (NOLOCK) ON C.RoleId = D.RoleId
WHERE D.RoleName IN (
SELECT LoweredRoleName FROM dbo.aspnet_Roles
WHERE LoweredRoleName NOT IN('expired','admin')
)
May 27, 2010 at 7:43 pm
Use a table-valued delimited-split function, with a cross apply, to handle this. The tvf returns the individual parts of the passed-in string as a table.
First, if you don't have the DelimitedSplit function:
IF OBJECT_ID('dbo.DelimitedSplit') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit
GO
CREATE FUNCTION [dbo].[DelimitedSplit] (
@list varchar(max),
@Delimiter char(1)
)
RETURNS TABLE
AS
RETURN
-- first, need to break down into separate items.
-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
ItemSplit (ItemOrder, Item) AS (
SELECT N,
RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,
CHARINDEX(@Delimiter,@Delimiter + @list + @Delimiter,N+1)-N-1)))
FROM Tally
WHERE N < LEN(@Delimiter + @list + @Delimiter)
AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = @Delimiter
)
SELECT ItemID = ROW_NUMBER() OVER (ORDER BY ItemOrder),
Item
FROM ItemSplit
GO
Now, to show how to use it:
DECLARE @test-2 TABLE (
ID int IDENTITY,
Col1 varchar(max)
)
-- make some data with two delimiters.
-- , is the minor delimitor.
INSERT INTO @test-2
SELECT 'BOB,Joe,Fred,John' UNION ALL
SELECT 'Jim,Billy,Greg,Laura' UNION ALL
SELECT 'Tim,Clinton,Sarah,Amanda' UNION ALL
SELECT 'Sam,Sandy'
SELECT OriginalRow = t1.ID,
SplitID = ds.ItemID,
ds.Item
FROM @test-2 t1
CROSS APPLY dbo.DelimitedSplit(t1.Col1, ',') ds
ORDER BY OriginalRow, FirstSplitID
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply