| Author |
 Topic  |
|
|
mspelly
Starting Member
2 Posts |
 Posted - 08/30/2012 : 15:14:49
|
Hi
I have the following two tables
/**********************************************************************************************/
/****** Object: Table [dbo].[FA_EventType] Script Date: 01/07/2012 22:01:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FA_EventType](
[EventType_ID] [smallint] IDENTITY(1,1) NOT NULL,
[Event_Type] [nvarchar](30) NOT NULL UNIQUE,
CONSTRAINT [PK_FA_EventType] PRIMARY KEY CLUSTERED
(
[EventType_ID] 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
/**********************************************************************************************/
/****** Object: Table [dbo].[FA_EventDetails] Script Date: 01/08/2012 12:29:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FA_EventDetails](
[EventDetail_ID] [bigint] IDENTITY(1,1) NOT NULL,
[Event_Ref_Number] [nvarchar](100) NULL,
[Event_Description] [nvarchar](3000) NOT NULL,
[Event_DateTime] [datetime] NOT NULL,
[User_ID] [nvarchar](50) NULL,
[Sys_ID] [smallint] NULL,
[ItemsUnderControl_ID] [int] NULL,
[EventType_ID] [smallint] NOT NULL,
CONSTRAINT [PK_FA_EvetDetails] PRIMARY KEY CLUSTERED
(
[EventDetail_ID] DESC
)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
ALTER TABLE [dbo].[FA_EventDetails] WITH CHECK ADD CONSTRAINT [FK_FA_EventDetails_FA_EventType] FOREIGN KEY([EventType_ID])
REFERENCES [dbo].[FA_EventType] ([EventType_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[FA_EventDetails] CHECK CONSTRAINT [FK_FA_EventDetails_FA_EventType]
GO
ALTER TABLE [dbo].[FA_EventDetails] WITH CHECK ADD CONSTRAINT [FK_FA_EventDetails_FA_ItemsUnderControl] FOREIGN KEY([ItemsUnderControl_ID])
REFERENCES [dbo].[FA_ItemsUnderControl] ([ItemUnderControl_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[FA_EventDetails] CHECK CONSTRAINT [FK_FA_EventDetails_FA_ItemsUnderControl]
GO
ALTER TABLE [dbo].[FA_EventDetails] WITH CHECK ADD CONSTRAINT [FK_FA_EventDetails_FA_Systems] FOREIGN KEY([Sys_ID])
REFERENCES [dbo].[FA_Systems] ([Sys_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[FA_EventDetails] CHECK CONSTRAINT [FK_FA_EventDetails_FA_Systems]
GO
ALTER TABLE [dbo].[FA_EventDetails] ADD CONSTRAINT [DF_FA_EventDetails_Event_Ref_Number] DEFAULT ('N/A') FOR [Event_Ref_Number]
GO
ALTER TABLE [dbo].[FA_EventDetails] ADD CONSTRAINT [DF_FA_EventDetails_Event_Description] DEFAULT ('N/A') FOR [Event_Description]
GO
I have following stored procedure
--Check if SPROC exists and if it does drop it
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_GetAllEventDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_GetAllEventDetails]
GO
CREATE PROCEDURE [dbo].[usp_GetAllEventDetails]
@DateStart DATETIME,
@DateEnd DATETIME,
@UserID NVARCHAR(50),
@EventType NVARCHAR(30),
@EventRefNum NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT
dbo.FA_EventDetails.Event_Ref_Number,
dbo.FA_EventDetails.Event_Description,
dbo.FA_EventDetails.Event_DateTime,
dbo.FA_EventType.Event_Type,
dbo.FA_EventDetails.User_ID
FROM
dbo.FA_EventDetails
INNER JOIN
dbo.FA_EventType ON dbo.FA_EventDetails.EventType_ID = dbo.FA_EventType.EventType_ID
WHERE
(dbo.FA_EventType.Event_Type LIKE @EventType) AND
(dbo.FA_EventDetails.User_ID LIKE @UserID) AND
(dbo.FA_EventDetails.User_ID LIKE @EventRefNum) AND
(dbo.FA_EventDetails.Event_DateTime
BETWEEN CONVERT(DATETIME, @DateStart, 102)
AND CONVERT(DATETIME, @DateEnd, 102))
ORDER BY
dbo.FA_EventDetails.Event_DateTime DESC
END
When I pass in wildcard '%' for the 3 variables @EventType, @UserID, @EventRefNum
I get the following returned
Event_Ref_Number Event_Description Event_DateTime Event_Type User_ID
--------------------------------------------------------------------------
2345-999 NS-02-99 WEST SWITCH 21/08/2012 20:18 SYSTEM ADD SPELLM
123457 NS-02-99 EAST SWITCH 21/08/2012 21:18 SYSTEM ADD SPELLM
PC0010 SYSTEm 21/08/2012 21:19 SYSTEM ADD SPELLM
I want to serach using wild cards for the Event_Ref_Number so when I pass in
23% for Event_Ref_Number I get zero records returned? I can't figure out why as in
my stored procedure I am using LIKE.
I recreated the stored procedure as a view in SSMS and using the filter passed in the
above wild card and I got the following returned which is what I would expect
Event_Ref_Number Event_Description Event_DateTime Event_Type User_ID
--------------------------------------------------------------------------
2345-999 NS-02-99 WEST SWITCH 21/08/2012 20:18 SYSTEM ADD SPELLM
I am stumped as I am a newbie to SQL so help appreciated |
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 08/30/2012 : 15:29:00
|
Are you passing in 23% for Event_Ref_Number, and % for the other 2?
-Chad |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/30/2012 : 15:34:30
|
You code itself looks fine from visual inspection. So you have to debug. I would start by just running these queries to see if they give expected results.SELECT TOP 10 Event_Type FROM dbo.FA_EventType
WHERE Event_Type LIKE '2345-999'
SELECT TOP 10 Event_Type FROM dbo.FA_EventType
WHERE Event_Type LIKE '2345%'
If they work as expected, add the remaining where conditions, one at a time until you find what the issue is. I am suspecting some prefixed characters or something not quite right in the parameters you pass in. |
|
|
|
mspelly
Starting Member
2 Posts |
Posted - 08/31/2012 : 15:03:54
|
Silly me Stored Procedure rerferencing the wrong column for the variable @EventRefNum
Was
dbo.FA_EventDetails.User_ID LIKE @EventRefNum
Should be
dbo.FA_EventDetails.Event_Ref_Number LIKE @EventRefNum
thx for all replies |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47035 Posts |
Posted - 08/31/2012 : 15:12:36
|
quote:
Originally posted by mspelly
Silly me Stored Procedure rerferencing the wrong column for the variable @EventRefNum
Was
dbo.FA_EventDetails.User_ID LIKE @EventRefNum
Should be
dbo.FA_EventDetails.Event_Ref_Number LIKE @EventRefNum
thx for all replies
Glad that you sorted it out yourself 
No way anyone of use could find it out as we dont know about your system
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 08/31/2012 : 15:32:51
|
quote:
Originally posted by visakh16
quote:
Originally posted by mspelly
Silly me Stored Procedure rerferencing the wrong column for the variable @EventRefNum
Was
dbo.FA_EventDetails.User_ID LIKE @EventRefNum
Should be
dbo.FA_EventDetails.Event_Ref_Number LIKE @EventRefNum
thx for all replies
Glad that you sorted it out yourself
No way anyone of use could find it out as we dont know about your system
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
That's funny, I actually noticed it, and almost mentioned it, but thought "He must know what those columns are better than I do"
-Chad |
|
|
| |
Topic |
|
|
|
Stored Procedure Not Working
