stored procedure not returning data for parameter

Question

stored procedure not returning data for parameter

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
June 24, 2020 at 9:44 am

#3764896
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
- This topic was modified 4 years, 6 months ago by frdrckmitchell7.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

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

Thom A SSC Guru Points: 99133 More actions · Answer 2

Sounds like you have a "catch-all" query, so NULL returns all rows, and the parameter you're passing doesn't have a =operator against the column you think it does.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 3

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

Thom A SSC Guru Points: 99133 More actions · Answer 4

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.

This reply was modified 4 years, 6 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 5

frdrckmitchell7

Ten Centuries

Points: 1351

June 24, 2020 at 12:04 pm

#3764914

Thanks! You're a star!

kind regards

Phil Parkin SSC Guru Points: 246987 More actions · Answer 6

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246987 More actions · Answer 7

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

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

Thom A SSC Guru Points: 99133 More actions · Answer 9

Phil Parkin wrote:

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