SQL Beginner

  • Hi everyone,

    I'm a beginner with SQL Server 2005 and trying to complete a project with the following question, can someone please help?

    Use AdventureWorks database and HumanResources.Department table.

     

    Create a stored procedure called spDepartmentAddUpdate.  This procedure accepts two parameters: Name, and GroupName.  The data types are VarChar(50), and VarChar(50) respectively.  Define logic in this procedure to check for an existing Department record with the same Name.  If the department record exists, update the GroupName and ModifiedDate. Otherwise, insert a new department record.

     

    1. Execute your stored procedure to show that the insert logic works.
    2. Execute your stored procedure to show that the update logic works.

      Note:

     

    1.        DepartmentID is an identity column.

    2.        You may use GETDATE() to insert or update the modified date.

    Thanks in advance!

  • Are you making us do your homework for you?

    Why dont you make an attempt and we can guide you through?

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • Sorry Dinakar, I was only trying to get some help not a complete solution.  Thanks anyways...

  • The SQL 2005 books online conain a lot of information for beginners. The sections on create procedure, insert statements, update statements and if - else statements will probably be of most use for this problem

    If you are stuck, we'll be happy to give advice and assistance

    So what, specifically, do you need help with?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • As stated before your going to use update and insert of course but look at the keyword

    EXISTS

    and the functions

    SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY (hint triggers and other events can affect the results of one or more of these)

     

  • Thank you, I used NOT EXIST with the IF...ELSE statement and it worked!

  • That person is probably in the same scenario as me. How does one speak Japanese when all they grew up with is African. There is no clue as to how to begin coding or what is required in code lines. I am in a SQL class and the problem I am having is beginning to write the coding. The text is variable and I cannot tell what the lines look like when they are typed. Is there anywhere that I can learn SQL like I am in kindergarten and specifically this is SQL server? I have three sets of information that I have to enter into and SQL format but not preset table to reference from. There is employee_id with its variable information, Job title with a few components, SQL_insert for a list of names; and with all of this I am to enter these records into a list of supplied job titles. I could do this layout in Cobol and develop a table in excel or word but there is not hint anywhere on basic SQL development. Can you help or do you have a resource?

  • Please start a new thread if you have a new question.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply