I am developing a stored procedure named sp_element_attributes using SQL Server 2005 for an Access 2003 application using Access as the front end and SQL Server as the back end database. I want to create a new table titled tblElement_Attributes with the following 3 fields:
create procedure sp_element_attributes
As
Begin
create table tblElement_Attributes
(
ELEMENT_TYPE varchar(255) null,
ELEMENT_VALUE varchar(255) null,
ATTRIBUTES varchar(255) null
)
The result of executing the stored procedure should generate the following records and values for the newly created table tblElement_Attributes:
ELEMENT_TYPE ELEMENT_VALUE ATTRIBUTES
------------------- ---------------------- ----------------
RIGHTS Everything NULL
RIGHTS Security Administrator NULL
RIGHTS User NULL
RIGHTS View NULL
I have another table titled tblSecurity with the following fields and values:
tblSecurity
KERBEROSID LASTNAME FIRSTNAME PASSWORD RIGHTS PASSWORDREVISED
--------------- -------------- -------------- -------------- --------- ---
zimmermr ZIMMER ROBERT flight34 E
smithg SMITH GEORGE jump21 S
helmane HELMAN ED turnip22 U
benettm BENETT MARY ocart32 V
trumanw TRUMAN WENDY tert33 S
The goal is to select the distinct values from the tblSecurity table field RIGHTS and populate them into the newly created table titled tblElement_Attributes. Thus, there will be 1 record in the newly created table tblElement_Attributes for each unique value of the field RIGHTS in table tblSecurity.
The field ELEMENT_TYPE in the tblElement_Attributes table has the static value "RIGHTS".
The field ATTRIBUTES in the tblElement_Attributes table has the static value "NULL".
The field ELEMENT_VALUE will have values as follows:
tblSecurity: RIGHTS tblElement_Attributes
--------- ----------------------------
E -> Everything
S -> Security Administrator
U -> User
V -> View