A function to return or create an ID value

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)

A function to return or create an ID value

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-11-30 : 17:06:44

I am trying to build a view that will display data from an existing MSSQL 2000 table with look-ups to display IDs instead of the name values in the table. If the name value doesn't exist, I want it to add it into the reference/look-up table.

Take the following look-up table of group ID's and Names:


CREATE TABLE #groups ( id int IDENTITY, [name] varchar(10) )
INSERT INTO #groups ([name]) VALUES ('Sales')
INSERT INTO #groups ([name]) VALUES ('Support')

Here is the report that is imported daily:


CREATE TABLE #report ( id int IDENTITY, [name] varchar(10), [group] varchar(10), [location] varchar(10) )
INSERT INTO #report ([name],[group],[location]) VALUES ('Jason','Sales','New York')
INSERT INTO #report ([name],[group],[location]) VALUES ('Steve','Sales','Paris')
INSERT INTO #report ([name],[group],[location]) VALUES ('Richard','Support','New York')

I want the view to return:


id | Name    | Group | Location
1  | Jason   |   1   | New York
2  | Steve   |   1   | Paris
3  | Richard |   2   | New York

And then if the report is updated the next day and a new marketing group is created:


INSERT INTO #report ([name],[group],[location]) VALUES ('Tim','Marketing','Paris')

I want it to add "Marketing" to the groups table when the view is called:


id | Name    | Group | Location
1  | Jason   |   1   | New York
2  | Steve   |   1   | Paris
3  | Richard |   2   | New York
4  | Tim     |   3   | Paris

I need to be able to create a look-up for Location as well (and the actual database has 9 columns that need to be looked up.)

I tried to create a function called getGroup that would return the ID number for the group after checking if it exists, but the following code doesn't work. From what I understand you can't call INSERT or call stored procedures from within a function. Is there an easy way to accomplish this?


CREATE FUNCTION getGroup (@value varchar(10)) 
  RETURNS int
  AS
    BEGIN
      IF NOT EXISTS (SELECT * FROM #groups WHERE name LIKE @value)
      INSERT INTO #groups([name]) VALUES (@value)
      @id = SELECT * FROM #groups WHERE name LIKE @value)
      RETURN @id
    END

CREATE VIEW showreport AS
    SELECT id,
           name,
           getGroup(group) As group,
           location
    FROM #report

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:35:56

why not use a procedure instead?


CREATE FUNCTION getFullDetails 
AS
      INSERT INTO #groups([name])
      SELECT r.group
      FROM #report r
      WHERE NOT EXISTS(SELECT 1 FROM #groups WHERE name = r.group)
      
      SELECT r.Name,g.id as [Group],r.Location    
      FROM #report r
      INNER JOIN #groups g
      ON g.name = r.group

 GO     

just call it like

EXEC GetFullDetails

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-12-01 : 10:41:38

That will work. Thank you!

I guess I was thinking along the lines of processing the records line by line rather then updating all the references as a separate stage. The actual database contains between 1000 and 2000 records and has 9 fields that need to be cross-referenced.

Would it be beneficial to call the distinct values rather than everything, or does it take the same resources to come up with the distincts?


CREATE PROCEDURE getFullDetails
AS
    INSERT INTO #groups([name])
    SELECT DISTINCT r.group FROM #report r
      WHERE NOT EXISTS(SELECT name FROM #groups WHERE name = r.group)
    ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:44:59

quote:
Originally posted by Jaypoc

That will work. Thank you!

I guess I was thinking along the lines of processing the records line by line rather then updating all the references as a separate stage. The actual database contains between 1000 and 2000 records and has 9 fields that need to be cross-referenced.

Would it be beneficial to call the distinct values rather than everything, or does it take the same resources to come up with the distincts?

CREATE PROCEDURE getFullDetails
AS
INSERT INTO #groups([name])
SELECT DISTINCT r.group FROM #report r
WHERE NOT EXISTS(SELECT name FROM #groups WHERE name = r.group)
...

yep...that seems to make sense. DISTINCT should be one used in this case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources