Strange Error with Stored Procedure

Question

 

I have the following stored procedure 

USE [KeyControl_Dev_DB]
GO
/****** Object:  StoredProcedure [dbo].[Key_SelectByValue]    Script Date: 01/13/2013 16:08:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Key_SelectByValue]
(
	@Value varchar(50),
	@ValueName varchar(50)
)
AS
	SET NOCOUNT OFF;
IF (@ValueName = 'File_Num') BEGIN
    SELECT        Series, Room, Door, Quantity, File_Num, Location, Department, Type, Justification,Active
     FROM            Key_Table 
     WHERE (Active = 'Yes') AND (File_Num = @Value )                        
        END   
              ELSE 
              if (@ValueName = 'Series')BEGIN
             SELECT        Series, Room, Door, Quantity, File_Num, Location, Department, Type, Justification,Active
             FROM            Key_Table 
             WHERE (Active = 'Yes') AND (Series  like '%'+@Value+'%')
              END 
              ELSE
              if (@ValueName = 'Room')BEGIN
              SELECT        Series, Room, Door, Quantity, File_Num, Location, Department, Type, Justification,Active
              FROM            Key_Table 
              WHERE (Active = 'Yes') AND (Room  like '%'+@Value+'%')   
              END 
              ELSE
              if (@ValueName = 'Location')BEGIN
              SELECT        Series, Room, Door, Quantity, File_Num, Location, Department, Type, Justification,Active
              FROM            Key_Table 
              WHERE (Active = 'Yes') AND (Location like '%'+@Value+'%')
              END 
               

and there are 2 records on my table first one with Location as (ooooo) and the second with Location as (yyyyyy) 

I executed the stored procedure 2 times, first time with @value = oo & @ValueName as Location  

second time with @value = yy  & @ValueName as Location 

strangely the first time doesn't return any records meanwhile it does return in the second time !!

what would be the reason !?  

Answer 1

strangely the first time doesn't return any records meanwhile it does return in the second time !!

I suspect a data anomaly that isn't obvious.  Maybe something like zeros instead of the letters "o".  The following returns both the expected rows:

CREATE TABLE dbo.Key_Table(
	Series varchar(10)
	, Room varchar(10)
	, Door varchar(10)
	, Quantity varchar(10)
	, File_Num varchar(10)
	, Location  varchar(10)
	, Department varchar(10)
	, Type varchar(10)
	, Justification varchar(10)
	, Active varchar(10)
	);

INSERT INTO dbo.Key_Table VALUES('001', '002', '003', '004', '005', 'ooooo', '006', '007', '008', 'yes');
INSERT INTO dbo.Key_Table VALUES('011', '012', '013', '014', '015', 'yyyyy', '016', '017', '018', 'yes');

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

Please try to run it using my script below and ignore the foreign key constraint, data insertion is included in the script

USE [KeyControl_Dev_DB]
GO
/****** Object:  Table [dbo].[Key_Table]    Script Date: 01/14/2013 10:03:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Key_Table](
	[File_Num] [varchar](50) NOT NULL,
	[Series] [varchar](50) NOT NULL,
	[Room] [varchar](50) NULL,
	[Door] [varchar](50) NULL,
	[Location] [varchar](50) NULL,
	[Quantity] [int] NULL,
	[Type] [varchar](50) NULL,
	[Department] [varchar](50) NULL,
	[Active] [varchar](50) NULL,
	[Justification] [varchar](100) NULL,
 CONSTRAINT [PK_Key_Table] PRIMARY KEY CLUSTERED 
(
	[File_Num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Key_Table] UNIQUE NONCLUSTERED 
(
	[Series] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Key_Table] ([File_Num], [Series], [Room], [Door], [Location], [Quantity], [Type], [Department], [Active], [Justification]) VALUES (N'1', N'AA1', N'GAD 01C', N'GAD 01A', N'TELEPHONE OPERATORS', 3, N'Door', N'ISD', N'Yes', NULL)
INSERT [dbo].[Key_Table] ([File_Num], [Series], [Room], [Door], [Location], [Quantity], [Type], [Department], [Active], [Justification]) VALUES (N'2', N'AA2', N'GAD 04', N'GAD 04', N'RECEPTION', 3, N'Door', N'ISD', N'Yes', NULL)
INSERT [dbo].[Key_Table] ([File_Num], [Series], [Room], [Door], [Location], [Quantity], [Type], [Department], [Active], [Justification]) VALUES (N'3', N'sdhet', N'grwerg', N'erhqer', N'qerhy', 4, N'Door', N'qwetwqet', N'Yes', N'')
INSERT [dbo].[Key_Table] ([File_Num], [Series], [Room], [Door], [Location], [Quantity], [Type], [Department], [Active], [Justification]) VALUES (N'5', N'dhjty', N'tytyery', N'ttttt', N'ooooo', 5, N'Locker', N'rrrrrrrr', N'no', N'')
INSERT [dbo].[Key_Table] ([File_Num], [Series], [Room], [Door], [Location], [Quantity], [Type], [Department], [Active], [Justification]) VALUES (N'6', N'fgjsryj', N'uetyuety', N'y we5ywe', N'yyyyyy', 3, N'Door', N'isd', N'Yes', NULL)
/****** Object:  Default [DF_Key_Table_Active]    Script Date: 01/14/2013 10:03:18 ******/
ALTER TABLE [dbo].[Key_Table] ADD  CONSTRAINT [DF_Key_Table_Active]  DEFAULT ((1)) FOR [Active]
GO

Answer 3

when I generate the stored procedure script there's a N prefix before my values which denotes that the subsequent string is in Unicode ! 

script below

USE [KeyControl_Dev_DB]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[Key_SelectByValue]
		@Value = N'oo',
		@ValueName = N'location'

SELECT	'Return Value' = @return_value

GO

do u think this has to do with the error ?

knowing that when I query the same way using new query window I do get the record back !

select*
from Key_Table
where Location like '%oo%'

Answer 4

do u think this has to do with the error ?

No, the Unicode string will be converted to the varchar parameter type.

knowing that when I query the same way using new query window I do get the record back !

select*
from Key_Table
where Location like '%oo%'

This is not the same way.  The WHERE clause in the proc includes Active = 'Yes' in the predicate. I think you will also not return the row in the ad-hoc query if you add that same condition since the Active value of this row is 'no'.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 5

    LOOOOOOOOL ;D

   Thank you Dan I it was a stupid *strange* mistake not to remember the other condition ;D XD 

Answer 6

well at least it's not me who wrote the proc and I'm not convinced that the condition Active = 'Yes' should be there, so my subconscious mind just ignored it ;D