December 18, 2017 at 1:46 pm
I run this stored procedure, the problem is that when i specify a Manager Lastname and Firstname which does not exists in the dbo.Employees table , the stored procedure returns just a blank cell instead of NULL and the wrong record gets inserted.
CREATE PROCEDURE dbo.insertemployee(
@DepartmentID INT,
@EmployeeFirstName NVARCHAR(50),
@EmployeeLastName NVARCHAR(50),
@Salary INT = 3000,
@ManagerFirstName NVARCHAR(50),
@ManagerLastName NVARCHAR(50)
)
AS
BEGIN;
DECLARE @ID INT
SELECT @ID = dbo.GetEmployeeID2(@ManagerFirstName, @ManagerLastName)
IF @ID =NULL
BEGIN;
INSERT INTO dbo.Employees(DepartmentID, FirstName, LastName)
VALUES(@DepartmentID, @ManagerFirstName, @ManagerLastName);
END;
INSERT INTO dbo.Employees( DepartmentID, ManagerEmployeeID, FirstName, LastName, Salary)
VALUES(@DepartmentID, @ID, @EmployeeFirstName, @EmployeeLastName, @Salary);
END;
-- run some tests with the stored procedure
execute dbo.insertemployee 1 , 'Jerry', 'yang', 32, 'Sarah', 'Campbell';
--test for ManagerID = NULL
execute dbo.insertemployee 1 , 'Jerry', 'men', 22 , 'Sara', 'Campbell';
SELECT * FROM dbo.Employees;
December 18, 2017 at 2:09 pm
Change the
IF @ID = NULL
to
IF @ID IS NULL
You cannot compare a value directly to NULL.
And that's not the only issue of course. If the employee does not yet exist you need to get his @ID somehow after the first INSERT, otherwise you will insert the manager with a NULL EmployeeID.
December 18, 2017 at 2:16 pm
Jan Van der Eecken - Monday, December 18, 2017 2:09 PMChange the
IF @ID = NULLto
IF @ID IS NULLYou cannot compare a value directly to NULL.
While this is correct advice, this proc looks a little weird to me. If @ID is NULL, you are inserting two rows to dbo.Employees, one with manager info (only) and one with employee info and a blank ManagerEmployeeID.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 20, 2017 at 8:25 pm
An integer salary? A company where the personnel have hundred character names written in Unicode Chinese? Why don’t you know that the expression “@generic_id = NULL†will always test to a constant value of unknown? The department identifier cannot be an integer or any other numeric; you’re not do any math on it (do you often take the square root of your credit card numbers?)
There is no such crap as @id in RDBMS. An identifier must identifysomething in particular, not some just vague generic entity.
SQL is a declarative language,, So we don’t like to use local variableslike are doing.
Using functions, because you don’t know how to write the SQL, prevents the optimizer from being able to do anything. That used to work in the old procedural languages.
Finally,you seem to believe that a manager is a totally different creature from an employee. In a properly designed schema, being a manager would be the value of a job title attribute. You’re still building assembly language pointer chains, and not writing RDBMS at all!
I would suggest you look up the nested set model of hierarchies and use that to build your personnel tables.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 26, 2017 at 12:59 pm
tomyang03 - Monday, December 18, 2017 1:46 PM
An integer salary? A company where the personnel have hundred character names written in Unicode Chinese? Why don’t you know that the expression “@generic_id = NULL†will always test to a constant value of unknown? The department identifier cannot be an integer or any other numeric; you’re not do any math on it (do you often take the square root of your credit card numbers?)
There is no such crap as @id in RDBMS. An identifier must identifysomething in particular, not some just vague generic entity.
SQL is a declarative language,, So we don’t like to use local variableslike are doing.
Using functions, because you don’t know how to write the SQL, prevents the optimizer from being able to do anything. That used to work in the old procedural languages.
Finally,you seem to believe that a manager is a totally different creature from an employee. In a properly designed schema, being a manager would be the value of a job title attribute. You’re still building assembly language pointer chains, and not writing RDBMS at all!
I would suggest you look up the nested set model of hierarchies and use that to build your personnel tables.
Although in general I agree with your post, the tone you presented it in is slightly aggressive. Not everyone is a trained DBA or developer and we would like to encourage accidental DBAs or part time SQL writers to ask questions without fear of being insulted or degraded, regardless of how simple or obvious a solution might be to us.
Perhaps the solution is to not only criticise as you have done, but rather to offer advice and examples in a manner that a layman can understand as I think this person may be.
December 26, 2017 at 1:56 pm
jcelko212 32090 - Wednesday, December 20, 2017 8:24 PMtomyang03 - Monday, December 18, 2017 1:46 PMAn integer salary? A company where the personnel have hundred character names written in Unicode Chinese? Why don’t you know that the expression “@generic_id = NULL†will always test to a constant value of unknown? The department identifier cannot be an integer or any other numeric; you’re not do any math on it (do you often take the square root of your credit card numbers?)
There is no such crap as @id in RDBMS. An identifier must identifysomething in particular, not some just vague generic entity.
SQL is a declarative language,, So we don’t like to use local variableslike are doing.Using functions, because you don’t know how to write the SQL, prevents the optimizer from being able to do anything. That used to work in the old procedural languages.
Finally,you seem to believe that a manager is a totally different creature from an employee. In a properly designed schema, being a manager would be the value of a job title attribute. You’re still building assembly language pointer chains, and not writing RDBMS at all!
I would suggest you look up the nested set model of hierarchies and use that to build your personnel tables.
Although in general I agree with your post, the tone you presented it in is slightly aggressive. Not everyone is a trained DBA or developer and we would like to encourage accidental DBAs or part time SQL writers to ask questions without fear of being insulted or degraded, regardless of how simple or obvious a solution might be to us.
Perhaps the solution is to not only criticise as you have done, but rather to offer advice and examples in a manner that a layman can understand as I think this person may be.
Good luck with that.
December 26, 2017 at 1:57 pm
kevaburg - Tuesday, December 26, 2017 12:59 PMjcelko212 32090 - Wednesday, December 20, 2017 8:24 PMtomyang03 - Monday, December 18, 2017 1:46 PMAn integer salary? A company where the personnel have hundred character names written in Unicode Chinese? Why don’t you know that the expression “@generic_id = NULL†will always test to a constant value of unknown? The department identifier cannot be an integer or any other numeric; you’re not do any math on it (do you often take the square root of your credit card numbers?)
There is no such crap as @id in RDBMS. An identifier must identifysomething in particular, not some just vague generic entity.
SQL is a declarative language,, So we don’t like to use local variableslike are doing.Using functions, because you don’t know how to write the SQL, prevents the optimizer from being able to do anything. That used to work in the old procedural languages.
Finally,you seem to believe that a manager is a totally different creature from an employee. In a properly designed schema, being a manager would be the value of a job title attribute. You’re still building assembly language pointer chains, and not writing RDBMS at all!
I would suggest you look up the nested set model of hierarchies and use that to build your personnel tables.
Although in general I agree with your post, the tone you presented it in is slightly aggressive. Not everyone is a trained DBA or developer and we would like to encourage accidental DBAs or part time SQL writers to ask questions without fear of being insulted or degraded, regardless of how simple or obvious a solution might be to us.
Perhaps the solution is to not only criticise as you have done, but rather to offer advice and examples in a manner that a layman can understand as I think this person may be.
Good luck with that.
+ a googolplex to the googolplex power...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply