March 10, 2008 at 4:20 pm
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';
March 10, 2008 at 5:28 pm
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;
March 10, 2008 at 8:29 pm
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
March 10, 2008 at 11:18 pm
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
END
"Keep Trying"
March 11, 2008 at 12:40 am
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'
March 11, 2008 at 1:08 am
If you dont get the error message then its confirmation that the deletion worked properly.
"Keep Trying"
March 11, 2008 at 4:35 am
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
March 11, 2008 at 5:36 am
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
March 11, 2008 at 5:39 am
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
March 11, 2008 at 5:59 am
Thats great, I'll have a play around with that tonight.
Thanks for all your help
March 12, 2008 at 8:41 am
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.
March 12, 2008 at 12:43 pm
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.
March 12, 2008 at 12:46 pm
J (3/12/2008)
ssnI 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:
March 12, 2008 at 12:55 pm
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?
March 12, 2008 at 1:10 pm
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply