CODEWITHSUNDEEP
sqlsql-server
Greg Schalk
Greg Schalk

Reputation: 73

Stored procedure not matching TSQL code results

I have a very simple stored procedure

SET ANSI_NULLS off
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[TESTPARTSTATUS]
@PartNumber nvarchar(50)
as
begin
select * from VPPAPStatus where PartNumber = @PartNumber
end

The results I get with most part numbers work out just fine, but there are some part numbers that do not return any result. some are due to there is no data but some there is data and I can query in SSMS and I see the expected results. like below:

select * from VPPAPStatus where PartNumber = '84578452'

This returns 2 results (as expected) but when the same PartNumber is is run in the stored procedure there is no result??

Updates to question based on comments:

new test stored procedure is

SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER On
GO
ALTER procedure [dbo].[TESTPARTSTATUS]
@PartNumber nvarchar(50)
as
begin
select * from dbo.VPPAPStatus where PartNumber = @PartNumber
select * from dbo.VPPAPStatus where PartNumber = '84578452'
select @partnumber as 'INPUTPN' 

end

when This runs, I get no results when selecting by @PartNumber, 2 results when selecting by '84578452' and the text '84578452' returned back as the InputPN

I am totally confused by this behavior any ideas where or what to check??

Upvotes: 1

Views: 157

Answers (1)

Greg Schalk
Greg Schalk

Reputation: 73

Frustrated and looking for a work around I used like LTrim(RTrim(@PartNumber))
Annoyingly, this worked right away.

Since I select from a View, I modified the view to trim the PN selection up front this solved the issue for any other SP's that use the same fields

Even though when I copied and pasted the values side by side in notepad and didn't see any special characters or spaces, there must have been something tripping up the stored procedure.

Lesson Learned - Make Sure data is trimmed before going into the database.

Upvotes: 1

Related Questions