While Loop - 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

Transact-SQL (2000)

While Loop

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2008-05-30 : 11:18:57

Hi

Can anyone tell me a better way of doing the following: I have a list of employers and for each I want to assign 3 roles. see code below. Basically i don't want to repeat the same code for each of the roles I am inserting,


DECLARE @CurrentRow int
DECLARE @RowCount int, 
	@RoleId int,
	@EmployerId int,
	@AuthId int,
	@RoleName varchar(50)

-- Insert all employers into temp table
DECLARE @temptable table(Id int primary key identity(1,1), EmployerId int)

-- get all employers
INSERT INTO @temptable (EmployerId)
SELECT DISTINCT EmployerId
FROM t_Org 
WHERE NOT EmployerId IS NULL

SELECT @CurrentRow = 1
SELECT @RowCount=COUNT(Id) FROM @temptable

BEGIN
	--For each record in @temptable give role to employer for user
	WHILE @CurrentRow <= @RowCount
	BEGIN
	
		SET @RoleName = 'Reference Table Manager'
	
		SELECT @RoleId = Id FROM t_RolesforUser WHERE [Name] = @RoleName
		
		SELECT  @EmployerId = EmployerId
		FROM  @temptable 
		WHERE Id = @CurrentRow
	
		
		INSERT INTO UsersRole(
		UId,
		RId,
		EmpId)
		SELECT 
		@AuthId,
		@RoleId,
		@EmployerId
	
		SELECT @CurrentRow = @CurrentRow + 1 
	END
	--For each record in @temptable give role to employer for user with role
	WHILE @CurrentRow <= @RowCount
	BEGIN
	
		
		SET @RoleName = 'Departmental Transfers'
	
		SELECT @RoleId = Id FROM t_RolesforUser WHERE [Name] = @RoleName
		
		SELECT  @EmployerId = EmployerId
		FROM  @temptable 
		WHERE Id = @CurrentRow
	
		
		INSERT INTO UsersRole(
		UId,
		RId,
		EmpId)
		SELECT 
		@AuthId,
		@RoleId,
		@EmployerId
	
		SELECT @CurrentRow = @CurrentRow + 1 
	END
		
	--For each record in @temptable give role to employer for user
	WHILE @CurrentRow <= @RowCount
	BEGIN
	
		SET @RoleName = 'National File Manager'
	
		SELECT @RoleId = Id FROM t_RolesforUser WHERE [Name] = @RoleName
		
		SELECT  @EmployerId = EmployerId
		FROM  @temptable 
		WHERE Id = @CurrentRow
	
		
		INSERT INTO UsersRole(
		UId,
		RId,
		EmpId)
		SELECT 
		@AuthId,
		@RoleId,
		@EmployerId
	
		SELECT @CurrentRow = @CurrentRow + 1 
	END
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-30 : 12:49:44

Think CROSS JOIN and use a derived table.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 12:56:21

Dont know why you are using WHILE loops for this?i thinkyou need only this?

DECLARE @Roles Table
(
RoleId   int,
RoleName varchar(50)
)


INSERT INTO @Roles 
SELECT DISTINCT Id,[Name] FROM t_RolesforUser 
WHERE [Name] IN('Reference Table Manager','Departmental Transfers','National File Manager')


INSERT INTO UsersRole(
		UId,
		RId,
		EmpId)
SELECT DISTINCT @AuthId,
		r.RoleId,
		t.EmployeeId
FROM t_Org t
CROSS JOIN @Roles r
WHERE EmployerId IS NOT NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 13:01:06

quote:
Originally posted by Peso

Think CROSS JOIN and use a derived table.

E 12°55'05.25"
N 56°04'39.16"

Did you read my mind?

Just finished giving a soln and found you stating same thing.

Looper
Yak Posting Veteran

68 Posts

Posted - 2008-06-02 : 04:18:05

Thats done the trick

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 05:04:56

I was just lucky. You did all the hard work.
All I did was thinking of this

INSERT INTO	UsersRole
		(
			UId,
			RId,
			EmpId
		)
SELECT DISTINCT	@AuthID,
		r.RoleID,
		t.EmployeeID
FROM		t_Org AS t
CROSS JOIN	(
			SELECT	RoleID
			FROM	t_RolesForUser
			WHERE	Name IN ('Reference Table Manager', 'Departmental Transfers', 'National File Manager')
		) AS r
WHERE		t.EmployerID IS NOT NULL

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources