Always getting empty set in stored procedure 2

[sparkbyte]

I cannot seem to figure out why this stored procedure alway returns an empty set.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:		John F Fuhrman III
-- Create date: 05/03/2010
-- Description:	File Number Lookup
-- =============================================
ALTER PROCEDURE [dbo].[usp_FileNumberLookup] 
	-- Add the parameters for the stored procedure here
	@strFileNumber varchar
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT	Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM	dbo.tblTrackingTable
WHERE	(FileNumber LIKE @strFileNumber 
			AND	FileNumber Not Like '.box.end.')
END

But this query works

SELECT	Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM	dbo.tblTrackingTable
WHERE	(FileNumber LIKE 'A12345678' 
			AND	FileNumber Not Like '.box.end.')

Thanks

John Fuhrman

 

[Crowley16]

have you tried to put the actual size behind the varchar?

change "@strFileNumber varchar"
to "@strFileNumber as varchar(50)"

--------------------
Procrastinate Now!

 

[gmmastros]

I agree with Crowley16. I blogged on this a while ago. The blog explains the issue pretty well.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-stored-procedure-with-nvarcha

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Give you both a star.

Crowley16, I can't believe it was that simple!! Thanks.

George, Tanks again!! great blog.

Lesson learned!! Be specific.


Thanks

John Fuhrman