Hey I've got a homework problem I'm working on and am stumped.

[Nep Tune]

It goes like the following:
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.

A. Execute your stored procedure to show that the insert logic works.
B. Execute your stored procedure to show that the update logic works.

Any hints from the wizards out there would be greatly appreciated!

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Nep Tune (ne*******@go.com) writes:

It goes like the following:
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.

A. Execute your stored procedure to show that the insert logic works.
B. Execute your stored procedure to show that the update logic works.

Any hints from the wizards out there would be greatly appreciated!

If you are not able to carry out your homework assignments, you should
talk to the teacher, rather than sneak behind his back. Your teacher
knows what you are supposed to know, and what you yet have to learn.

But permit me to not that the parameters to the procedure should really
be nvarchar(50) and not varchar(50) to go along with the table definition.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Nep Tune]

YOU WRITE:
you should talk to the teacher, rather than sneak behind his back. Your
teacher knows what you are supposed to know, and what you yet have to
learn.
I WRITE:
Well, thanks for the suggestions. You and my teacher ought to team
teach; I'd learn about the same from both of you: NOTHING!

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Nep Tune (ne*******@go.com) writes:

I WRITE:
Well, thanks for the suggestions. You and my teacher ought to team
teach; I'd learn about the same from both of you: NOTHING!

Ah, but there is a difference! He is paid to teach you to nothing.

On a more serious note, newsgroups are not the best place to learn. If
someone asks a "how do I write this query", it can be fairly simple to
write that query, provided that the question is clear enough. But
explaining what is actually happening can be a lot more difficult, and
I usually don't do that in my posts. That's OK if the poster has some
experience and has run into a little more difficult problem at work.
If he is interested he will try to understand the solution and use it.

If he is not interested, well at least he got help with doing his part
in his work. After all, it could be that SQL is something he does left-
hand and his main skills are with VB, C++ or whatever. And I would expect
him to be able to write the procedure you were asked to.

Anyway, some hints: you will need to use INSERT and UPDATE. You will also
learn to master IF EXISTS. If you ever grow up to be a developer, you
will write tons of such procedures in your career! :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

On 12 Aug 2006 13:59:56 GMT, Nep Tune <ne*******@go.comwrote:

>YOU WRITE:
you should talk to the teacher, rather than sneak behind his back. Your
teacher knows what you are supposed to know, and what you yet have to
learn.
I WRITE:
Well, thanks for the suggestions. You and my teacher ought to team
teach; I'd learn about the same from both of you: NOTHING!

What the hell do you expect, when your initial post amounts to "please
do my entire assignment for me"? Now if you demonstrated some prior
effort on your own part - e.g. "I tried so-and-so, but it gets
such-and-such wrong, and I don't know how to proceed from there" -
then I bet people would be a lot more willing to help out.

[--CELKO--]

> Create a stored procedure called spDepartmentAddUpdate. <<

The "sp-" prefix violates ISO-11179 rules. Even Microsoft gave up on
camelCase because it is a bitch to read.

>This procedure accepts two parameters: Name, and GroupName. <<

"name" is too vague to be a data element -- naem of what?

>The data types are VarChar(50), and VarChar(50) respectively. <<

See prior remark about camelCase. This should be NVARCHAR(n) where (n)
was actually researched . Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

> Define logic in this procedure to check for an existing Department record [sic] with the same Name [was this the key in the DDL you did not post?]. If the department record [sic] exists, update the GroupName and ModifiedDate. Otherwise, insert a new department record [sic]. <<

Rows are not records; fields are not columns; tables are not files.
This is foundations.

No auditor will allow you to put "modified_date" in a table. Audit
trails have to be separate from the data by law (see SOX compliance
rules), by GAAP and by common sense.

>Any hints from the wizards out there would be greatly appreciated! <<

At every school I have taught or at which I have been a student,
presenting the work of other people as your own will get you kicked
out. So far, I have ended the college education of two cheaters, one
in New Zealand and one in the US by reporting them to their
departments. Is that enough of a hint?

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

>>The data types are VarChar(50), and VarChar(50) respectively. <<

See prior remark about camelCase. This should be NVARCHAR(n) where (n)
was actually researched . Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc.

No, Joe. Get yourself a copy of SQL 2005 and install the AdventureWorks
database. NepTune may be cheating with his homework. But he gave all
necessary information to solve the problem.

No auditor will allow you to put "modified_date" in a table. Audit
trails have to be separate from the data by law (see SOX compliance
rules), by GAAP and by common sense.

I don't think AdventureWorks Bicycles has any auditor...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx