SQL Query - Loop and using variable
Posted on 2012-09-05
I have a query that I would like to do a loop through number of users and use the userid as variable. I been googling and don't find a good resource. Can someone help out?
DECLARE @USER VARCHAR(50)
SET @USER = 'SELECT DISTINCT USERID FROM DB1.DBO.SECURITY_COMPONENT'
WHILE (SELECT COUNT(A.USERID) FROM (SELECT DISTINCT USERID FROM DB1.DBO.SECURITY_COMPONENT) A ) > 0
SELECT @USERID AS USER,
SC.DESCRIPTION, SC.COMPONENT_ID, SC.COMPONENT,
CASE WHEN A.PERMISSION IS NULL THEN 'Y' ELSE A.PERMISSION END AS PERMISSION,
FROM DB1.DBO.SECURITY_COMPONENT SC
LEFT OUTER JOIN
SELECT T.USER_ID, T.DESCRIPTION, T.COMPONENT_ID, T.COMPONENT, T.PERMISSION, T.SUB_COMPONENT
FROM DB1.DBO.SECURITY_COMPONENT T
WHERE S.USER_ID = @USER
) A ON A.COMPONENT = SC.COMPONENT
AND A.COMPONET_ID = SC.COMPONENT_ID
WHERE SC.USERID = 'NOPERMISSION'
I want to loop through each user.