June 24, 2020 at 9:44 am
Hi clever people.
I have two tables (employees and department - departmentID connects the two) in a stored procedure. I have a parameter (name) in the stored procedure so when I say EXEC uspReturnEmployee 'Sales' I get zero rows. When I execute procedure without the parameter input I get all the data including sales. Can anyone guide me as to get, for example, 'sales' rows?
thanks guys!
CREATE PROCEDURE uspReturnEmployees
@departmentName VARCHAR = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT employeeNo
,lastName
,firstName
,gender
,IDNumber
,salaryLeveLID
,departmentID
FROM Employees E
INNER JOIN Department D
ON E.departmentID = D.departmentID
WHERE name = @departmentName
OR ISNULL(@departmentName, '') = ''
END
June 24, 2020 at 10:24 am
Without seeing the query? Nope. Sorry. Sounds like you don't have the parameter in the WHERE clause maybe? I don't know because I can't see your code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2020 at 11:35 am
CREATE PROCEDURE uspReturnEmployees
@departmentName VARCHAR = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT employeeNo
,lastName
,firstName
,gender
,IDNumber
,salaryLeveLID
,departmentID
FROM Employees E
INNER JOIN Department D
ON E.departmentID = D.departmentID
WHERE name = @departmentName
OR ISNULL(@departmentName, '') = ''
END
June 24, 2020 at 11:51 am
Bad habits to kick : declaring VARCHAR without (length). The varchar'sales' is never going to fit in a varchar(1); it's 4 characters too long.
Also, don't use ISNULL(@DepartmentName,'') = '', Use @DepartmentName IS NULL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2020 at 12:04 pm
Thanks! You're a star!
kind regards
June 24, 2020 at 12:50 pm
Another tip for future posts – for improved readability, please use code blocks when posting code:
CREATE PROCEDURE uspReturnEmployees @departmentName VARCHAR(50) = NULL
AS
SET NOCOUNT ON;
SELECT employeeNo
,lastName
,firstName
,gender
,IDNumber
,salaryLeveLID
,departmentID
FROM Employees E
JOIN Department D
ON E.departmentID = D.departmentID
WHERE name = @departmentName
OR @departmentName IS NULL;
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
June 24, 2020 at 12:52 pm
One final tip: best practice is to qualify all of your object names with their schema names.
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
June 24, 2020 at 1:54 pm
Thanks for taking on all the good comments everyone. I was away for a bit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2020 at 2:55 pm
Another tip for future posts – for improved readability, please use code blocks when posting code:
CREATE PROCEDURE uspReturnEmployees @departmentName VARCHAR(50) = NULL
AS
SET NOCOUNT ON;
SELECT employeeNo
,lastName
,firstName
,gender
,IDNumber
,salaryLeveLID
,departmentID
FROM Employees E
JOIN Department D
ON E.departmentID = D.departmentID
WHERE name = @departmentName
OR @departmentName IS NULL;
I forgot to mention, considering the type of query you have here, then you'll want to ensure you don't cache bad plans. Considering the simplicity of the query here, I would suggest added OPTION (RECOMPILE), so that the query plan isn't reused for the wrong query (for example, using a plan where @departmentName has a non-NULL value for a query where it does).
For more complex queries, then going down the dynamic approach can be better, in my opinion, as each different version of the query can have a different plan cached. This also means the data engine doesn't need to recompile the plan every time, when doing so could be expensive itself.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply