troubleshooting Question

How to create a stored procedure to generate a new table using SQL Server 2005?

Avatar of zimmer9
zimmer9Flag for United States of America asked on
Microsoft SQL Server 2005
1 Comment1 Solution300 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros