SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure returns a blank cell instead of NULL


stored procedure returns a blank cell instead of NULL

Author
Message
tomyang03
tomyang03
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 3
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;
Jan Van der Eecken
Jan Van der Eecken
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8230 Visits: 6556
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.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211203 Visits: 24523
Jan Van der Eecken - Monday, December 18, 2017 2:09 PM
Change the

IF @ID = NULL


to

IF @ID IS NULL


You 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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Joe Celko
Joe Celko
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6279 Visits: 3502
[quote]
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.

Please post DDL and follow ANSI/ISO standards when asking for help.
kevaburg
kevaburg
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16014 Visits: 1283
jcelko212 32090 - Wednesday, December 20, 2017 8:24 PM
[quote]
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385963 Visits: 42552
kevaburg - Tuesday, December 26, 2017 12:59 PM
jcelko212 32090 - Wednesday, December 20, 2017 8:24 PM
[quote]
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.


Good luck with that.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sgmunson
sgmunson
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89903 Visits: 6925
Lynn Pettis - Tuesday, December 26, 2017 1:56 PM
kevaburg - Tuesday, December 26, 2017 12:59 PM
jcelko212 32090 - Wednesday, December 20, 2017 8:24 PM
[quote]
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.


Good luck with that.


+ a googolplex to the googolplex power...

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search