Making meta data - 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)

Making meta data

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-01-28 : 07:24:24

I have two tables: tblUser and tblUserDepartment.

On user can be linked to more Departments.

Now I want to display the departments of a user in a list e.g.

Username Department(s)
Mark Sales, Administration
Laura Administration
Hank Sales, Finance, Board

I thought of making a meta column in tblUsers and making it a VARCHAR. Then loop through the deparments and making a departments string and put that in tblUser.

Bad thing is this meta data has te be checked every now and then and calculated whenever a change is made in tblUserDepartment.

Good thing is I don't have te recalculate te departments a user is in every time I make a list of users.

Any suggestions? (of course, NOT cursor based).

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-01-28 : 07:34:57

This is what I made (for one specific user)

Gebruiker = user
Afdeling = department


CREATE PROCEDURE [dbo].[spRunGebruikerAfdeling] 

	@Gebruiker NUMERIC = NULL

AS


/* 
Versie	0.01	- 28-01-2005	Henri Koppen

vul de kolom mAfdeling bij Gebruikers
*/


DECLARE @Teller INT
DECLARE @Huidig INT
DECLARE @uid INT
DECLARE @mAfdeling VARCHAR(8000)

SELECT @Teller = COUNT(*) FROM tblGebruikerAfdeling WHERE Gebruiker = @Gebruiker

IF @Teller = 0
	RETURN -- Moet eigenlijk NEXT zijn

SET @Huidig = 0
SET @mAfdeling = ''
SET @uid = 0
-- Teller is het aantal afdelingen dat opgehaald moet worden

WHILE @Huidig < @Teller
	BEGIN
		SELECT @uid = ga.uid, @mAfdeling = LTRIM (@mAfdeling) + ' ' + a.Naam FROM tblGebruikerAfdeling ga JOIN tblAfdeling a ON a.uid = ga.Afdeling WHERE ga.Gebruiker = @Gebruiker AND ga.Uid > @uid

		SET @Huidig = @Huidig + 1

	END

UPDATE tblGebruiker SET mAfdeling = @mAfdeling WHERE uid = @Gebruiker

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-01-28 : 08:47:11

Hmm, I am the only one posting at my own topic. Pathetic...

However I'm pretty pleased with my procedure now.

If there's only One user, it does the work for one user. If none is specified, do all users and clean the ones that are not linked to any department (afdeling).

And, no cursor used.

However, is it smart this way? of silly? (mAfdeling being the metadata column)


CREATE PROCEDURE [dbo].[spRunGebruikerAfdeling] 

	@Gebruiker NUMERIC = NULL

AS


/* 
Versie	0.01	- 28-01-2005	Henri Koppen

vul de kolom mAfdeling bij Gebruikers
*/


DECLARE @Teller INT
DECLARE @Huidig INT
DECLARE @uid INT
DECLARE @mAfdeling VARCHAR(8000)
DECLARE @Gebruikers INT

-- Kijken of er een gebruiker is meegegeven, anders alle gebruikers
IF @Gebruiker IS NOT NULL 
	BEGIN
		SET @Gebruikers = 1
	END
ELSE
	BEGIN
		SELECT @Gebruikers = COUNT(*) FROM tblGebruiker g WHERE EXISTS (SELECT * FROM  tblGebruikerAfdeling ga WHERE ga.Gebruiker = g.uid )-- Alleen gebruikers die een of meerdere afdelingen hebben
		UPDATE tblGebruiker SET mAfdeling = NULL FROM tblGebruiker g WHERE NOT EXISTS  (SELECT * FROM  tblGebruikerAfdeling ga WHERE ga.Gebruiker = g.uid)
	END


WHILE @Gebruikers > 0 
	BEGIN

		IF @Gebruiker IS NULL
			SELECT TOP 1 @Gebruiker = g.uid FROM tblGebruiker g WHERE EXISTS (SELECT * FROM  tblGebruikerAfdeling ga WHERE ga.Gebruiker = g.uid)  ORDER BY g.uid

		-- Aantal afdelingen berekenen
		-- Teller is het aantal afdelingen dat opgehaald moet worden
		SELECT @Teller = COUNT(*) FROM tblGebruikerAfdeling WHERE Gebruiker = @Gebruiker
		SET @Huidig = 0
		SET @mAfdeling = ''
		SET @uid = 0

		WHILE @Huidig < @Teller AND @Teller IS NOT NULL
			BEGIN
				SELECT @uid = ga.uid, @mAfdeling = LTRIM (@mAfdeling) + ' ' + a.Naam FROM tblGebruikerAfdeling ga JOIN tblAfdeling a ON a.uid = ga.Afdeling WHERE ga.Gebruiker = @Gebruiker AND ga.Uid > @uid

				SET @Huidig = @Huidig + 1

			END

		IF LEN(@mAfdeling) > 0
			UPDATE tblGebruiker SET mAfdeling = LTRIM (@mAfdeling) WHERE uid = @Gebruiker

		SELECT TOP 1 @Gebruiker = g.uid FROM tblGebruiker g WHERE EXISTS (SELECT * FROM  tblGebruikerAfdeling ga WHERE ga.Gebruiker = g.uid)  AND  g.uid > @Gebruiker ORDER BY g.uid		

		SET @Gebruikers = @Gebruikers - 1
	END

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-28 : 11:07:12

Converting Multiple Rows into a CSV String (Set Based Method)

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources