Writing the code for a stored procedure

Question

Post reply

Writing the code for a stored procedure

Mikebyrne000

SSC-Addicted

Points: 464
More actions
March 10, 2008 at 4:20 pm

#183889

I need to write a new stored procedure to delete from a table named employee. (need to delete the entire row)
The primary key for this table is the ssn field
Not sure how to code this as a stored procedure but i assume the sql would be:
DELETE FROM employee WHERE ssn='ssn';

Viewing 15 posts - 1 through 15 (of 26 total)

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

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Almost. Your code deletes records whose ssn equals the string 'ssn'. You want a variable instead.

create proc up_EmpDelete

@SSN char(9)

as

DELETE FROM employee WHERE ssn=@ssn;

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 2

To follow up on the code presented by ksullivan:

create proc up_EmpDelete

@SSN char(9)

as

DELETE FROM employee WHERE ssn=@ssn;

You would then execute the stored proc as follows: to delete all records from employee where ssn is '123-45-6789'

EXEC up_EmpDelete '123-45-6789'

I also suggest you qualify the stored proc with a schema, such as:

CREATE PROCEDURE sales.up_EmpDelete

@SSN char(9)

AS

DELETE FROM employee WHERE ssn = @ssn;

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Add error handling

CREATE PROCEDURE sales.up_EmpDelete

@SSN char(9)

AS

BEGIN

DELETE FROM employee WHERE ssn = @ssn;

IF @@Error <> 0

BEGIN

RAISERROR 50001 ' Error while deleting from employee'

END

"Keep Trying"

Mikebyrne000 SSC-Addicted Points: 464 More actions · Answer 4

Thsts a great help guys as I was just using 'ssn' and couldnt figure out why it wasnt deleting.

I like the statment

IF @@Error <> 0

BEGIN

RAISERROR 50001 ' Error while deleting from employee'

END

Is there a way of inserting a confirmation msg such a 'SSN @ssn HAS BEEN REMOVED'

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

If you dont get the error message then its confirmation that the deletion worked properly.

"Keep Trying"

Mikebyrne000 SSC-Addicted Points: 464 More actions · Answer 6

No I know that I was just wondering if it was posible to code a confirmation msgbox to to show the user that the action has taken place

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

You can either raise a "success" error or have the app process the "RETURN" code, or both...

CREATE PROCEDURE sales.up_EmpDelete

@SSN CHAR(9)

AS

--===== Declare local variables

DECLARE @MyError INT

--===== Do the required delete

DELETE FROM dbo.Employee WHERE SSN = @SSN

--===== Capture the error information

SELECT @MyError = @@ERROR

--===== Process any errors...

IF @MyError <> 0 --Error occurred

BEGIN

RAISERROR 50001 ' Error while deleting from employee'

RETURN -1 --Error indication

END

ELSE

BEGIN

RAISERROR ('Row deleted',10,1) WITH NOWAIT

RETURN 0 --No errors

END

What you really need to do is lookup CREATE PROCEDURE and ERROR HANDLING in Books Online... it comes free with SQL Server and is the "Help" system that we lovingly refer to as just "BOL". Seriously, make it your best friend... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Almost forgot... as of SQL Server 2005, SQL Server can also use a form of Try/Catch. I'd don't use it because I never got into "programming by exception" because it's a really expensive thing for an error to happen in SQL Server... so I write code that figures out if something is going to cause an error and, if so, don't allow the code to do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mikebyrne000 SSC-Addicted Points: 464 More actions · Answer 9

Thats great, I'll have a play around with that tonight.

Thanks for all your help

J-440512 SSCertifiable Points: 6285 More actions · Answer 10

ssn

I would be weary about using the social security number, especially if you have an employee code or number. ssn should in my humble view used only for matters dealing with a government revenue department and should be kept confidential. What business does a DBA have in using sensitive piece of information for database management?

Also, are there any instances where the primary key for an employee was inserted in a "Changed by" field in other tables? For instance if an employee was the "Engineer of Record" for a project closed 2 years earlier, it is not necessarily a good idea the delete the employee record from the database, unless nobody cares about closed projects.

Bob Fazio SSChampion Points: 10794 More actions · Answer 11

Mikebyrne000 (3/11/2008)
No I know that I was just wondering if it was posible to code a confirmation msgbox to to show the user that the action has taken place

Not with T-SQL (remember SP and such are meant to be behind the scenes). As shown there are methods to alert the caller of the SP that something happened (good or bad) but SSMS isn't really for writing applications.

You can do it fairly easy with C#, vb Script, PowerShell, etc. But those are all for other Forums.

Bob Fazio SSChampion Points: 10794 More actions · Answer 12

J (3/12/2008)
ssn
I would be weary about using the social security number, especially if you have an employee code or number. ssn should in my humble view used only for matters dealing with a government revenue department and should be kept confidential. What business does a DBA have in using sensitive piece of information for database management?
Also, are there any instances where the primary key for an employee was inserted in a "Changed by" field in other tables? For instance if an employee was the "Engineer of Record" for a project closed 2 years earlier, it is not necessarily a good idea the delete the employee record from the database, unless nobody cares about closed projects.

Great advice, however I think most of us are assuming this is Homework. However, all the criteria for getting help was supplied. The OP gave what they have done so far, and what they were trying to do.

However, if I am wrong in my assumption, Sorry :blush:

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Chirag (3/11/2008)
If you dont get the error message then its confirmation that the deletion worked properly.

Remember though - a deletion being successful does NOT mean it found something and deleted it. It means that IF there was something to delete, then it was deleted. It's perfectly okay for there not to be anything to delete.

An error would occur if there WAS something to delete and it wasn't deleted for some reason.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Bob Fazio SSChampion Points: 10794 More actions · Answer 14

Another example (sorry didn't use yours) This also detects Nothing to do situation

DECLARE @status BIGINT

BEGIN TRANSACTION

BEGIN TRY

DELETE

FROM [master].[dbo].[tblAppLog]

WHERE tblAppLog.entry_num = @EntryID

SELECT @status = @@ROWCOUNT

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

SELECT @status = -1

END CATCH

-- Now determine what to do

-- @status > 0 means @status rows deleted

-- @status = 0 Nothing to delete

-- @status = -1 There was an error